Excel 2003 subtracting two Times 23:10 - 6:00am 2

[DougP]

A person started the Job at 11:10pm and ended the next day at 6:00am
here is my formula
=IF(A1>A2,(A2+24)-A1,A2-A1)
and it retuns 6:50 whihc is correct.
We need it to be in Minutes which in this case is 410 Minutes.
but no matter how I format it I can't get 410.
if I modify the fomrula to
=IF(A1>A2,(A2+24)-A1,A2-A1)*60
I get 2:00am or I get 1397

Can someone help me with this?
Which format cell do I choose?


DougP

 

[AnotherHiggins]

First, you need to understand how Excel deals with dates and times.

Here's the short version:
- Days are whole numbers, based on how many days it is after Jan 1, 1900. Today is 39545 because it is 39,545 days after 1/1/1900.
- Times are stored as a fraction of 24 hours. noon is 0.5 (half of 24), 6 PM = 18:00 = 0.75 (two-thirds of 24 hours).

When you add 24, you're actually adding 24 DAYS, not 24 hours.

so you'll want to change the formula to:
[tab][COLOR=blue white]=IF(A1>A2,A2+[red]1[/red]-A1,A2-A1)[/color]
[tab][tab](I also got rid of unnecessary parentheses)
and also change the format of the cell to
[tab][m]
The brackets tell Excel to display the total number of minutes, even if it goes over 1 hour.

For more info on Dates and Times in Excel, see faq68-5827


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

John's excellent solution DISPLAYS minutes, but the VALUE is still in DAYS.

To convert from days to minutes, take your result and factor by 24 * 60 (x days * 24 hr/day * 60 min/hr ==> x*24*60 MINUTES).

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[DougP]

thank you both
John , I missed the [] brackets around the "M" the first time. then I came back an read it more closely.
Works Great!


DougP