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

  • Thread starter Thread starter Lady_Aleena
  • Start date Start date
L

Lady_Aleena

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.
 
My Lady:
I expect someone could write a single, very long Excel formula to do this
but I am not going to try.
If you would be happy with a UDF (user defined function) in VBA please let
me know and I may have time to try it (if my sovereign lady give me time off
this weekend)

To learn more able VBA visit;

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Your obedient servant
 
Dear Bernard;

This is not time sensitive, no pun intended, so whatever time you can spare
to help me will be appreciated. I have been using Excel for years and know
that I have not even scratched the surface on what it can do. Even if you
just help thaw me out just a little with a nudge (the beginnings of it) would
be appreciated.

LA
 
Hi,

My head is now aching. This assumes your formula is in a1

=IF(INT(INT(A1)/168)>0,INT(INT(A1)/168)&" weeks
","")&IF(MOD(INT(A1),168)>=24,INT(MOD(INT(A1),168)/24)&" Days
","")&IF(AND(MOD(INT(A1),24)<24,MOD(INT(A1),24)<>0),MOD(INT(A1),24)&" Hours
","")&RIGHT(TEXT(A1/24,"h:mm"),2)&" mins"

I'm afraid the for 1 week or day it still uses the Plural of weeks or days.
We'll see if someone cracks that

Mike

Mike
 
Grrrrr,

I forgot to test for zero minutes, try this instead

=IF(INT(INT(A1)/168)>0,INT(INT(A1)/168)&" weeks
","")&IF(MOD(INT(A1),168)>=24,INT(MOD(INT(A1),168)/24)&" Days
","")&IF(AND(MOD(INT(A1),24)<24,MOD(INT(A1),24)<>0),MOD(INT(A1),24)&" Hours
","")&IF(MOD(A1,1)>0,RIGHT(TEXT(A1/24,"h:mm"),2)&" mins","")

Mike
 
Lady_Aleena said:
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.
Click to expand...


A1=E38/H37/2


=IF(INT(A1/168),INT(A1/168)&" week"&
IF(INT(A1/168)>1,"s","")&IF(MOD(A1,168),", ",""),"")&
IF(INT(MOD(A1,168)/24),INT(MOD(A1,168)/24)&"
day"&IF(INT(MOD(A1,168)/24)>1,"s","")&IF(MOD(A1,24),", ",""),"")&
IF(INT(MOD(A1,24)),INT(MOD(A1,24))&"
hour"&IF(INT(MOD(A1,24))>1,"s","")&IF(MOD(A1,1),", ",""),"")&
IF(MOD(A1,1),INT(MOD(A1,1)*60)&" minute"&IF(MOD(A1,1)*60>1,"s",""),"")
 
This should crack the plurals

=IF(INT(INT(A1)/168)>0,INT(INT(A1)/168)&" week","")&IF(INT(INT(A1)/168)>1,"s
"," ")&IF(MOD(INT(A1),168)>=24,INT(MOD(INT(A1),168)/24)&"
Day","")&IF(INT(MOD(INT(A1),168)/24)>1,"s ","
")&IF(AND(MOD(INT(A1),24)<24,MOD(INT(A1),24)<>0),MOD(INT(A1),24)&" Hour","
")&IF(MOD(INT(A1),24)>1,"s ","
")&IF(MOD(A1,1)>0,RIGHT(TEXT(A1/24,"h:mm"),2)&" min","
")&IF(MOD(INT(A1),24)>1,"s","")

Mike
 
Mike H;

Thank you so very much for all the work that you did to help me with this. I
really appreciate the time that you took to figure this out. I may end up
going with Glenn's solution, but know that your solution is appreciated!

LA
 
Glenn;

As with Mike H, I really appreciate you stepping in to help me. I really
should have been able to figure this out on my own, but I don't know as much
about Excel as I really should. I just realized that I made it more difficult
with the commas. I was thinking grammatically correct for sentence structure
when writing my initial post. Don't worry about them though. I will figure
out how to get them out.

Mike H and Glenn, have a very nice day!

LA
 
I think that you will find that my solution handles the plurals and commas
correctly.

Unless I missed something...
 
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").
 
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
 
Glenn;

You missed me being an idiot. I realized only after the initial post that I
did NOT want the commas. I will figure out how to remove them, you did a
really great job. Thanks!

LA
 
Try this value in A1...

=1.01694444444444

I get your formula showing this...

1 hour, 1 minutes
 
I gave you the wrong code, but never mind that... give this formula a try:

=TRIM(SUBSTITUTE(SUBSTITUTE(INT(A9/168)&" weeks ","0 weeks",""),
"1 weeks","1 week")&SUBSTITUTE(SUBSTITUTE(INT((A9-168*INT(A9/168))/24)&
" days","0 days",""),"1 days","1 day")& SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(TEXT((A9-168*INT(A9/168))/24," h"" hours"" m"" minutes"""),
" 0 hours","")," 0 minutes","")," 1 hours"," 1 hour")," 1 minutes",
" 1 minute"))
 
Rick said:
Try this value in A1...

=1.01694444444444

I get your formula showing this...

1 hour, 1 minutes
Click to expand...


Right, I missed an INT() around the last test. Converted to remove the commas
that weren't necessary:

=TRIM(IF(INT(A1/168),INT(A1/168)&
" week"&IF(INT(A1/168)>1,"s "," "),"")&
IF(INT(MOD(A1,168)/24),INT(MOD(A1,168)/24)&
" day"&IF(INT(MOD(A1,168)/24)>1,"s "," "),"")&
IF(INT(MOD(A1,24)),INT(MOD(A1,24))&
" hour"&IF(INT(MOD(A1,24))>1,"s "," "),"")&
IF(MOD(A1,1),INT(MOD(A1,1)*60)&
" minute"&IF(INT(MOD(A1,1)*60)>1,"s "," "),""))


which is a little shorter than your version, which I like.
 
Try this value in A1...
Right, I missed an INT() around the last test. Converted to remove the
commas that weren't necessary:

=TRIM(IF(INT(A1/168),INT(A1/168)&
" week"&IF(INT(A1/168)>1,"s "," "),"")&
IF(INT(MOD(A1,168)/24),INT(MOD(A1,168)/24)&
" day"&IF(INT(MOD(A1,168)/24)>1,"s "," "),"")&
IF(INT(MOD(A1,24)),INT(MOD(A1,24))&
" hour"&IF(INT(MOD(A1,24))>1,"s "," "),"")&
IF(MOD(A1,1),INT(MOD(A1,1)*60)&
" minute"&IF(INT(MOD(A1,1)*60)>1,"s "," "),""))


which is a little shorter than your version, which I like.
Click to expand...

While my code is longer than yours character-wise (SUBSTITUTE is such a long
function name<g>), it uses less than half as many individual function calls.
I could have made my code shorter yet, except that I kept bumping into
Excel's limit on the amount of nested functions calls it allows.
 
Rick said:
While my code is longer than yours character-wise (SUBSTITUTE is such a
long function name<g>), it uses less than half as many individual
function calls. I could have made my code shorter yet, except that I
kept bumping into Excel's limit on the amount of nested functions calls
it allows.
Click to expand...


How about this:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
" "&INT(A1/168)&" weeks "&INT(MOD(A1,168)/24)&" days ",
" 0 weeks","")," 1 weeks","1 week")," 0 days","")," 1 days"," 1 day")&
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
TEXT(MOD(A1,24)/24," h"" hours "" m"" minutes"""),
" 0 minutes","")," 1 minutes","1 minute")," 0 hours","")," 1 hours","1 hour"))


Also fixes a problem with multiples of 10 weeks.
 
Try this value in A1...
How about this:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
" "&INT(A1/168)&" weeks "&INT(MOD(A1,168)/24)&" days ",
" 0 weeks","")," 1 weeks","1 week")," 0 days","")," 1 days"," 1 day")&
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
TEXT(MOD(A1,24)/24," h"" hours "" m"" minutes"""),
" 0 minutes","")," 1 minutes","1 minute")," 0 hours","")," 1 hours","1
hour"))

Also fixes a problem with multiples of 10 weeks.
Click to expand...

GOOD CATCH!!!!
 
Back
Top