How can I calculate a restore time between two dates in Excel? 1

[mlrmlr]

Hello,

Can someone help me out with this Excel calculation please.

I am trying to get the restore time between two dates. The following is an example. I need to know how many hours and or minutes it took to restore this workorder.

Start Date Start Time End Date Restore Time

5/10/2006 12:00 AM 5/10/2006 2:00 PM

Thank you.

 

[AnotherHiggins]

You could put date and time in the same cell if you wanted. But since they are separated right now, you can use this:

(assuming your data is in A22)
[tab][COLOR=blue white]=(C2+D2)-(A2+B2)[/color]

This works because dates are stored as a whole number - the number of days since Jan 1, 1900. Today is 38848.

Times are stored as fractions of 24 hours. Noon is 0.5, 6PM is 0.75.

So just add the whole number and decimal together to get the date & time.

For more info on how Excel deals with dates and times, see faq68-5827, "Why do Dates and Times seem to be so much trouble?".

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

Help us help you. Please read FAQ181-2886 before posting.

 

[mlrmlr]

John,

I actually don't need the date, just the number of total minutes (120).

Thank you.

 

[AnotherHiggins]

Could the start and end dates ever be different? If so, this formula will account for that and still work when the dates are the same.

If you are positive that they will never be different dates, then you can (obviously) just use =D2-B2.

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

Help us help you. Please read FAQ181-2886 before posting.

 

[mlrmlr]

I think I got it. Thank you

 

[AnotherHiggins]

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

Help us help you. Please read FAQ181-2886 before posting.

 

[IsaacKite]

If you add the dates and times together, you will get the decimal equivalent of the number of days since Jan 1900. As correctly stated, this is just formatted to look like a date and a time. This is why you can add the dates and times together and then subtract the two totals to find the time difference, irrespective of whether or not the days are different.

The good news is that if you multiply this resulting date/time figure by 24 and reformat the result as number, this will give you the number of decimal hours elapsed. To find the number of minutes, multiply your date/time figure by 1440 (i.e., 60*24) instead, formatting as number to get the exact number of minutes. Will work every time. Just test it initially to check your formulas are right!

 

[SkipVought]

Isaac,

If you read the FAQ as posted above by John, you would see that these facts have been stated.

As a minor correction to your post, you do not have to sum the date and time in order to get the number of days since 1/1/1900.

The key truth is that TIME is in units of DAYS. Hence any grade-school kid should know how to convert days to hours or days to minutes or days to seconds, as the case may be.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}