|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#11
July 18th, 2009, 01:42 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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" wrote in message ... 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. 
|
|
#12
July 18th, 2009, 07:06 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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" wrote in message ... 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.
|
|
#13
July 18th, 2009, 07:08 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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 "Glenn" wrote: I think that you will find that my solution handles the plurals and commas correctly. Unless I missed something... Lady_Aleena wrote: 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 "Glenn" wrote: Lady_Aleena wrote: 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. 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)*601,"s",""),"")
|
|
#14
July 18th, 2009, 09:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
Try this value in A1...
=1.01694444444444 I get your formula showing this... 1 hour, 1 minutes -- Rick (MVP - Excel) "Glenn" wrote in message ... I think that you will find that my solution handles the plurals and commas correctly. Unless I missed something... Lady_Aleena wrote: 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 "Glenn" wrote: Lady_Aleena wrote: 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. 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)*601,"s",""),"")
|
|
#15
July 18th, 2009, 09:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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 (MVP - Excel) "Lady Aleena" wrote in message ... 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" wrote in message ... 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.
|
|
#16
July 20th, 2009, 02:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
Rick Rothstein wrote:
Try this value in A1... =1.01694444444444 I get your formula showing this... 1 hour, 1 minutes 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.
|
|
#17
July 20th, 2009, 02:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
Try this value in A1...
=1.01694444444444 I get your formula showing this... 1 hour, 1 minutes 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. While my code is longer than yours character-wise (SUBSTITUTE is such a long function nameg), 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 (MVP - Excel)
|
|
#18
July 20th, 2009, 03:57 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
Rick Rothstein wrote:
Try this value in A1... =1.01694444444444 I get your formula showing this... 1 hour, 1 minutes 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. While my code is longer than yours character-wise (SUBSTITUTE is such a long function nameg), 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. 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.
|
|
#19
July 20th, 2009, 04:06 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
Glenn wrote:
Also fixes a problem with multiples of 10 weeks. and 11 weeks, 21 weeks, 31 weeks, etc.
|
|
#20
July 20th, 2009, 04:32 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
Try this value in A1...
=1.01694444444444 I get your formula showing this... 1 hour, 1 minutes 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. While my code is longer than yours character-wise (SUBSTITUTE is such a long function nameg), 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. 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. GOOD CATCH!!!! -- Rick (MVP - Excel)
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
