|
| 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 |
|
#1
July 17th, 2009, 06:48 PM
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
July 17th, 2009, 07:26 PM
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
July 17th, 2009, 08:02 PM
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
July 17th, 2009, 08:11 PM
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
July 17th, 2009, 08:16 PM
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
July 17th, 2009, 08:29 PM
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
July 17th, 2009, 08:36 PM
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
July 17th, 2009, 09:02 PM
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
July 17th, 2009, 09:15 PM
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
July 17th, 2009, 09:34 PM
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",""),"")
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
