Hi,
I need an Excel formula to calculate the diffence between two dates and times.
For example, how many days, minutes and hours are there between the
1200 Hrs 1st January 2004 and 1600 Hrs 1st March 2004.
Date and time is expressed in the current spreadsheet :-e.g. 01/01/2004 12:00:00
I would need the exact formatting of the result cell to be expressed in :-
xx Days xx Hours xx Minutes.
Also, the formula must express the number of days to be greater than
31. A simple =A1-B1 equation cannot express greater than 31 days in
Excel.
Many thanks
Paul |
Clarification of Question by
pauloamore-ga
on
24 Apr 2004 12:08 PDT
I should add that date and time are expressed in the UK format of Date
/ Month / Year.
mikomoro - I need the equation used ; Assuming Cell A1 is the Start
date and time and Cell B1 is the End date and time.
The date and time suggested is merely as an example.
thx
p
|
Clarification of Question by
pauloamore-ga
on
24 Apr 2004 12:15 PDT
=SUM(B1-A1) can only add up to 31 Days 23 HOurs and 59 Minutes.
After that, days are misrepresented by Excel.
Thanks for your time.
p
|
Clarification of Question by
pauloamore-ga
on
24 Apr 2004 14:04 PDT
Guys,
Thanks for looking at this.
I need the number of days, HOURS AND MINUTES.
I need it expressed as xx Days xx HOurs and xxs Minutes.
I am not disputing that Excel can count the number of days. But I need
the formula to express as above. Excel counts up to 31 days 59 mins
and 59 seconds. If you calculate over that amount, the days are wrong.
I am using Excel 2000.
|
Request for Question Clarification by
efn-ga
on
24 Apr 2004 15:01 PDT
Hi Paul,
Does this meet your need?
=CONCATENATE(ROUND(ROUND(((B1-A1)*1440),0)/1440,0)," Days
",TRUNC(MOD(ROUND((B1-A1)*1440,0),1440)/60)," Hours
",ROUND(MOD(ROUND((B1-A1)*1440,0),60),0), " Minutes")
It ignores Daylight-Saving Time and doesn't show negative times
correctly, and I used month-day-year format input, but still it might
work for you. I believe the input date format is a configuration
option, not something one needs to program in a formula, so if you can
enter dates in day-month-year format now and Excel understands them,
that should not be a problem.
If this seems to be on the right track, I can post it as an answer, or
tweak it if it is close, but not quite right.
--efn
|
Clarification of Question by
pauloamore-ga
on
25 Apr 2004 07:26 PDT
Hi,
Your equation is close.
MM/DD/YY is okay. Works okay in UK format too.
There is a problem with hours and minutes though.
For example :-
Difference between 0220 Hrs on 3rd February 2004 and 0220 Hrs on 1st
January 2004 is calculated at 33 days, 2 hours and twenty minutes. 33
days is correct but obviously hours and minutes are incorrect.
Keep up the good work!
thx
p
|
Request for Question Clarification by
efn-ga
on
25 Apr 2004 09:38 PDT
Hi Paul,
Thanks for your response. I tried it with those values and it showed
0 Hours 0 Minutes. I even copied the formula from this web page and
it still worked, although I had to run it through a text editor to
convert line breaks to spaces first.
So there is probably some difference between your spreadsheet and mine
somehow. I am also using Excel 2000, so I don't think a difference in
Excel is the problem. If you typed the formula in, you could check
it. (I was able to reproduce your result by substituting A1 or B1 for
the second and third occurrences of B1-A1.) If you are comfortable
with usng a text editor, you could try copying, editing, copying, and
pasting the formula if you didn't do that before. As a check on the
data, if you display A1 as a number, it should be 37987.0972222222,
and B1 should be 38020.0972222222.
If you would like to copy the formula from your spreadsheet and post
it here, I can check it for differences compared to mine.
The coding could be simplified if we used another cell. You could put
the expression =ROUND(((B1-A1)*1440),0), which is the difference in
minutes, into another cell and replace the occurrences of that
expression with references to the cell. That might make it easier to
see what's going on. If you want to try that, but you don't want to
do the editing, let me know and I can give you the formula.
Those are all the ideas I have at this point for diagnosing why it
didn't work in your test.
--efn
|
Clarification of Question by
pauloamore-ga
on
25 Apr 2004 14:47 PDT
Hi,
I transposed the cell numbers to my spreadsheet incorrectly.
Your equation is correct!
Many thanks,
Please list as an answer!
:-)
p
|
Hi Paul,
Thanks for accepting my answer:
=CONCATENATE(ROUND(ROUND(((B1-A1)*1440),0)/1440,0)," Days
",TRUNC(MOD(ROUND((B1-A1)*1440,0),1440)/60)," Hours
",ROUND(MOD(ROUND((B1-A1)*1440,0),60),0), " Minutes")
As I noted, the line breaks in the formula should be replaced by
spaces, and the formula could be simplified with the use of another
cell.
Regards,
--efn |
Request for Answer Clarification by
pauloamore-ga
on
18 Nov 2004 03:46 PST
Me again! So sorry.
Just found a fault with the equation.
Please download an example here
www.ifb.co.uk/~stewart2/Excel/
Note that 1st equation fails, 2nd works and third as an example
Am willing to tip an error check / answer.
Regards
Paul
|
Clarification of Answer by
efn-ga
on
18 Nov 2004 19:11 PST
Hi Paul,
This may be a Summer Time issue.
When I look at your spreadsheet here in the USA, I may not see what
you see. For the first example in your spreadsheet, I get 2 days, 23
hours, 0 minutes for 10/30/2004 1:00 to 11/1/2004 0:00. This seems
right, ignoring any time change. Did you get a different answer? Or
did you get this answer and think it is wrong due to Summer Time?
--efn
|
Request for Answer Clarification by
pauloamore-ga
on
19 Nov 2004 03:06 PST
I think the answer should be 1 day 23 hours.
00:00 being the 1st time in any day as opposed to the last which would be 24:00
Thanks for looking at this,
Paul
|
Clarification of Answer by
efn-ga
on
19 Nov 2004 07:27 PST
Oops--you are right, of course.
Congratulations, you have found a bug in the formula. It's easy to
fix: just replace the first occurrence of "ROUND" with "TRUNC" so it
reads
=CONCATENATE(TRUNC(ROUND(((B1-A1)*1440),0)/1440,0)," Days
",TRUNC(MOD(ROUND((B1-A1)*1440,0),1440)/60)," Hours
",ROUND(MOD(ROUND((B1-A1)*1440,0),60),0), " Minutes")
(again replacing the line breaks with spaces)
As this was my error, I apologize and decline any further payment.
Regards,
--efn
|
