| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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 "Bernard Liengme" wrote: 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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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 "Lady_Aleena" wrote: 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 "Bernard Liengme" wrote: 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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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 "Mike H" wrote: 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 "Lady_Aleena" wrote: 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 "Bernard Liengme" wrote: 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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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",""),"") |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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),M OD(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" wrote: 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 "Mike H" wrote: 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 "Lady_Aleena" wrote: 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 "Bernard Liengme" wrote: 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 -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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 "Mike H" wrote: 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),M OD(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" wrote: 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 "Mike H" wrote: 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 "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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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",""),"") |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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",""),"") |
|
#11
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
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
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
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
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
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
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 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 (MVP - Excel) |
|
#18
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 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. 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
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
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 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. 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) |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
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!!!! Oh, and I forgot... good fix too! -- Rick (MVP - Excel) |
|
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Excel 2003 - Help writing a formula to get time (w, d, h, m)
Rick Rothstein wrote:
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!!!! Oh, and I forgot... good fix too! Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple calculations within cell? Writing in Excel 2000, using inExcel 2003 | Excel Worksheet Functions | |||
| Scrolling in Excel 07 while writing a formula - Please help!!! | Excel Discussion (Misc queries) | |||
| need help writing a formula in excel | New Users to Excel | |||
| Writing formula for excel worksheet | New Users to Excel | |||
| Help writing an Excel Formula | Excel Worksheet Functions | |||
Linear Mode
