Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
In a worksheet column I have a begining date in month, day, year, hour,
minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
If you have entered dates then the format of hours and minutes is not relevant.
If you have entered dates and times then the question is do you want to display the results in one cell and what do you want it to look like: 12.4:25 meaning 12 days 4 hours and 25 minutes or do you want it to display: "12 days 4 hours and 25 minutes" or do you want the days in one cell the hours in another and the minutes in yet another? For example, here is one option =INT(C1-A1)&" Days "&TEXT(MOD(C1-A1,1),"[hh]:mm") -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
If your data are genuine Excel dates and times, rather than text, then you
can use the formula =INT(B1-A1)&" days "&TEXT(MOD(B1-A1,1),"hh:mm") -- David Biddulph "Oscar A. Brown" <Oscar A. wrote in message ... In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
Shane:
I will try to make it clearer on what i need, since i'm new at this. In column A the info reads, 01/04/2009 11:45pm In column B the info reads, 01/06/2009 01:10pm i want the ending datain column C to read 1 day 13 hours 25 minutes. "Shane Devenshire" wrote: If you have entered dates then the format of hours and minutes is not relevant. If you have entered dates and times then the question is do you want to display the results in one cell and what do you want it to look like: 12.4:25 meaning 12 days 4 hours and 25 minutes or do you want it to display: "12 days 4 hours and 25 minutes" or do you want the days in one cell the hours in another and the minutes in yet another? For example, here is one option =INT(C1-A1)&" Days "&TEXT(MOD(C1-A1,1),"[hh]:mm") -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
=INT(B1-A1)&" day"&IF(INT(B1-A1)1,"s "," ")&
HOUR(B1-A1)&" hour"&IF(HOUR(B1-A1)1,"s "," ")& MINUTE(B1-A1)&" minute"&IF(MINUTE(B1-A1)1,"s","") Oscar A. Brown wrote: Shane: I will try to make it clearer on what i need, since i'm new at this. In column A the info reads, 01/04/2009 11:45pm In column B the info reads, 01/06/2009 01:10pm i want the ending datain column C to read 1 day 13 hours 25 minutes. "Shane Devenshire" wrote: If you have entered dates then the format of hours and minutes is not relevant. If you have entered dates and times then the question is do you want to display the results in one cell and what do you want it to look like: 12.4:25 meaning 12 days 4 hours and 25 minutes or do you want it to display: "12 days 4 hours and 25 minutes" or do you want the days in one cell the hours in another and the minutes in yet another? For example, here is one option =INT(C1-A1)&" Days "&TEXT(MOD(C1-A1,1),"[hh]:mm") -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
First step is to select your data and use Edit/ Replace to change
pm to pm (ie. space pm) to get the data from text into a format which excel regards as a data and time. Then use =INT(B1-A1)&" day "&TEXT(MOD(B1-A1,1),"hh"" hours"" mm"" minutes""") -- David Biddulph "Oscar A. Brown" wrote in message ... Shane: I will try to make it clearer on what i need, since i'm new at this. In column A the info reads, 01/04/2009 11:45pm In column B the info reads, 01/06/2009 01:10pm i want the ending datain column C to read 1 day 13 hours 25 minutes. "Shane Devenshire" wrote: If you have entered dates then the format of hours and minutes is not relevant. If you have entered dates and times then the question is do you want to display the results in one cell and what do you want it to look like: 12.4:25 meaning 12 days 4 hours and 25 minutes or do you want it to display: "12 days 4 hours and 25 minutes" or do you want the days in one cell the hours in another and the minutes in yet another? For example, here is one option =INT(C1-A1)&" Days "&TEXT(MOD(C1-A1,1),"[hh]:mm") -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
Actually, change all of the "" to "<" so you get "0 minutes" instead of "0
minute". Glenn wrote: =INT(B1-A1)&" day"&IF(INT(B1-A1)1,"s "," ")& HOUR(B1-A1)&" hour"&IF(HOUR(B1-A1)1,"s "," ")& MINUTE(B1-A1)&" minute"&IF(MINUTE(B1-A1)1,"s","") Oscar A. Brown wrote: Shane: I will try to make it clearer on what i need, since i'm new at this. In column A the info reads, 01/04/2009 11:45pm In column B the info reads, 01/06/2009 01:10pm i want the ending datain column C to read 1 day 13 hours 25 minutes. "Shane Devenshire" wrote: If you have entered dates then the format of hours and minutes is not relevant. If you have entered dates and times then the question is do you want to display the results in one cell and what do you want it to look like: 12.4:25 meaning 12 days 4 hours and 25 minutes or do you want it to display: "12 days 4 hours and 25 minutes" or do you want the days in one cell the hours in another and the minutes in yet another? For example, here is one option =INT(C1-A1)&" Days "&TEXT(MOD(C1-A1,1),"[hh]:mm") -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
Thanks for the feedback, see David's post for a slight change that will make
the display the way you want. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: Shane: I will try to make it clearer on what i need, since i'm new at this. In column A the info reads, 01/04/2009 11:45pm In column B the info reads, 01/06/2009 01:10pm i want the ending datain column C to read 1 day 13 hours 25 minutes. "Shane Devenshire" wrote: If you have entered dates then the format of hours and minutes is not relevant. If you have entered dates and times then the question is do you want to display the results in one cell and what do you want it to look like: 12.4:25 meaning 12 days 4 hours and 25 minutes or do you want it to display: "12 days 4 hours and 25 minutes" or do you want the days in one cell the hours in another and the minutes in yet another? For example, here is one option =INT(C1-A1)&" Days "&TEXT(MOD(C1-A1,1),"[hh]:mm") -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
David:
The Formula you gave me worked, except that when i calculate the day hour & minutes on paper I come up with 1 day 13 hours 25 minutes. the formula when it calculated it came back with 1 day 13 hours 10 minutes. what's up with that. Thanks Oscar "David Biddulph" wrote: First step is to select your data and use Edit/ Replace to change pm to pm (ie. space pm) to get the data from text into a format which excel regards as a data and time. Then use =INT(B1-A1)&" day "&TEXT(MOD(B1-A1,1),"hh"" hours"" mm"" minutes""") -- David Biddulph "Oscar A. Brown" wrote in message ... Shane: I will try to make it clearer on what i need, since i'm new at this. In column A the info reads, 01/04/2009 11:45pm In column B the info reads, 01/06/2009 01:10pm i want the ending datain column C to read 1 day 13 hours 25 minutes. "Shane Devenshire" wrote: If you have entered dates then the format of hours and minutes is not relevant. If you have entered dates and times then the question is do you want to display the results in one cell and what do you want it to look like: 12.4:25 meaning 12 days 4 hours and 25 minutes or do you want it to display: "12 days 4 hours and 25 minutes" or do you want the days in one cell the hours in another and the minutes in yet another? For example, here is one option =INT(C1-A1)&" Days "&TEXT(MOD(C1-A1,1),"[hh]:mm") -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Needed
It sounds as if you need to look carefully at the values in your data cells.
Either that or recheck the formula. Your result would be consistent with =INT(B1-A1)&" day "&TEXT(MOD(B1,1),"hh"" hours"" mm"" instead of =INT(B1-A1)&" day "&TEXT(MOD(B1-A1,1),"hh"" hours"" mm"" If you tried to retype the formula, don't do that. Copy the formula from the newsgroup and paste it into the formula bar of your cell. -- David Biddulph "Oscar A. Brown" wrote in message ... David: The Formula you gave me worked, except that when i calculate the day hour & minutes on paper I come up with 1 day 13 hours 25 minutes. the formula when it calculated it came back with 1 day 13 hours 10 minutes. what's up with that. Thanks Oscar "David Biddulph" wrote: First step is to select your data and use Edit/ Replace to change pm to pm (ie. space pm) to get the data from text into a format which excel regards as a data and time. Then use =INT(B1-A1)&" day "&TEXT(MOD(B1-A1,1),"hh"" hours"" mm"" minutes""") -- David Biddulph "Oscar A. Brown" wrote in message ... Shane: I will try to make it clearer on what i need, since i'm new at this. In column A the info reads, 01/04/2009 11:45pm In column B the info reads, 01/06/2009 01:10pm i want the ending datain column C to read 1 day 13 hours 25 minutes. "Shane Devenshire" wrote: If you have entered dates then the format of hours and minutes is not relevant. If you have entered dates and times then the question is do you want to display the results in one cell and what do you want it to look like: 12.4:25 meaning 12 days 4 hours and 25 minutes or do you want it to display: "12 days 4 hours and 25 minutes" or do you want the days in one cell the hours in another and the minutes in yet another? For example, here is one option =INT(C1-A1)&" Days "&TEXT(MOD(C1-A1,1),"[hh]:mm") -- If this helps, please click the Yes button Cheers, Shane Devenshire "Oscar A. Brown" wrote: In a worksheet column I have a begining date in month, day, year, hour, minute format, in the next column I have a ending month, day, year , hour, minute format. I need to calculate the elapsed days, hours, mintues from the being to the end. PLEASE HELP ME IF YOU CAN. I don't need to join the ranks of unemployment. Thanks Oscar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help needed | Excel Worksheet Functions | |||
Excel formula to copy/paste formula needed please. | Excel Discussion (Misc queries) | |||
formula needed for this one... | Excel Worksheet Functions | |||
Formula Help Needed | Excel Discussion (Misc queries) | |||
formula needed | New Users to Excel |