Formula Needed

[Oscar A. Brown]

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

 

[Shane Devenshire]

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")

 

[Oscar A. Brown]

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.

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

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

 

[Glenn]

=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","")

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.

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

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

 

[Glenn]

Actually, change all of the ">" to "<>" so you get "0 minutes" instead of "0
minute".

=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","")

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.

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


:

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

 

[Shane Devenshire]

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

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.

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

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

 

[Oscar A. Brown]

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

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

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.

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


:

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