Re: Excel 2003 - Help writing a formula to get time (w, d, h, m)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dear Rick;
The two numbers are not dates.

e38 = total widgets needed
h37 = widgets gained every half hour
(e38/h37)/2 = hours needed to gain the desired amount of widgets

Most of the time, the amount of time is less than a week, however in some
rare cases the amount of time is over a week. I was not looking for a date,
just the time needed in weeks, days, hours, and minutes. I am still reading
over what you wrote, trying to get a grip on it. Please forgive me being so
slow.

LA

"Rick Rothstein" wrote:

If you are up for a UDF (User Defined Function), try this one...

Press Alt+F11 to go to the VB editor, click Insert/Module from its menu bar,
then copy paste the following UDF (User Defined Function) into the code
window that opened up (see rest of my message after the code)...

'*************** START OF CODE ***************
Function YMWD(ByVal Date1 As Variant, ByVal Date2 As Variant) As String
Dim TempDate As Date
Dim NumOfYears As Long
Dim NumOfMonths As Long
Dim NumOfWeeks As Long
Dim NumOfDays As Long
If IsDate(CDate(Date1)) And IsDate(CDate(Date2)) Then
Date1 = CDate(Date1)
Date2 = CDate(Date2)
If Date1 > Date2 Then
TempDate = Date1
Date1 = Date2
Date2 = TempDate
End If
NumOfYears = DateDiff("yyyy", Date1, Date2)
Date1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
If Date1 > Date2 Then
Date1 = DateAdd("yyyy", -1, Date1)
NumOfYears = NumOfYears - 1
End If
NumOfMonths = DateDiff("m", Date1, Date2)
Date1 = DateSerial(Year(Date2), Month(Date2), Day(Date1))
If Date1 > Date2 Then
Date1 = DateAdd("m", -1, Date1)
NumOfMonths = NumOfMonths - 1
End If
NumOfDays = Abs(DateDiff("d", Date1, Date2))
NumOfWeeks = NumOfDays \ 7
NumOfDays = NumOfDays Mod 7
If NumOfYears > 0 Then
YMWD = CStr(NumOfYears) & " year" & _
IIf(NumOfYears = 1, "", "s")
End If
If NumOfMonths > 0 Then
If YMWD <> "" Then YMWD = YMWD & ", "
YMWD = YMWD & CStr(NumOfMonths) & " month" & _
IIf(NumOfMonths = 1, "", "s")
End If
If NumOfWeeks > 0 Then
If YMWD <> "" Then YMWD = YMWD & ", "
YMWD = YMWD & CStr(NumOfWeeks) & " week" & _
IIf(NumOfWeeks = 1, "", "s")
End If
If NumOfDays > 0 Then
If YMWD <> "" Then YMWD = YMWD & ", "
YMWD = YMWD & CStr(NumOfDays) & " day" & _
IIf(NumOfDays = 1, "", "s")
ElseIf YMWD = "" Then
YMWD = "0 Days"
End If
YMWD = RTrim$(YMWD)
End If
End Function
'*************** END OF CODE ***************

Now, go back to a worksheet and type this in...

=YMWD(A1,B1)

where I assume A1 and B1 contain the two *dates* that you want to find the
difference between. Note that the UDF puts the "s" on multiple units and
leaves it off for single units (that is, for example, "1 Week", but "2
Weeks").

--
Rick (MVP - Excel)


"Lady_Aleena" <Lady_Aleena@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E982D685-0127-4F10-A28C-24A4F7E8007B@xxxxxxxxxxxxxxxx
I have been going around in circles trying to write a formula to get weeks,
days, hours, and minutes.

(e38/h37)/2 is the amount of hours.

The format I would like the output to have is:

X week(s), X day(s), X hour(s), X minute(s)

If one doesn't apply, I would like it to NOT be displayed.

This should be easy, but with all of the rounddowns, mods, etc. plus
having
all the text added into all of the if statements to get the plurals right,
I
am just frozen.


.

Relevant Pages