Calculating the difference between dates

Hello All,

I wonder if anyone can help me, i am really struggling with this one! Basically i want to be able to take the received date/ time and compare it to the current date/time and give me a output of the difference.

E.g.

Receive Date - 07/07/2010
Receive Time - 15:46:07

Current Date - 12/07/2010
Current Time - 19:16:37

Difference Between - dd/mm hh:mm

This may seem simple for one but i have over 10,000 of these to check, i am currently doing the following:

In one cell i add up the receive date/time to give me it in one cell. In the next cell along i do =NOW() and in the next cell i minus the =NOW() cell from the cell i have put the receive date/time together.

It doesnt seem to quite work, it doesnt seem to be able to calculate it when the difference between is less than a month, it puts it as 1 month and blah blah blah hours. I am really confused.

Ignoring what i have done or not if the case, can someone come up with a solution as it will make my life soooo much easier at work.

Thanks,
Matt

Hello Matt,

Yes you are doing the correct calculation but formatting as dd/mm doesn't work because the mm always gives you calendar months, i.e. 1 to 12 whereas you need it to be 0 to 11

Simplest way is to just subtract the dates and format as a number to get a decimal representation of the difference, e.g. showing 45 days 8 hours as 45.33 using

=NOW()-A1-B1

....but if you want the result formatted to show days.....you could do the subtraction and just get days and hours like this

=INT(NOW()-A1-B1)&" days "&TEXT(NOW()-A1-B1,"hh:mm")

or use DATEDIF like this

=DATEDIF(A1,TODAY()-(MOD(NOW(),1)<B1),"m")&" months "&DATEDIF(A1,TODAY()-(MOD(NOW(),1)<B1),"md")&" days "&TEXT(NOW()-A1-B1,"hh:mm")

where A1 has your date and B1 your time

Hi,

If you wanted to highlight on the same sheet, then using daddylonglegs first and second formulas (for some reason can't get it to work with the third) in first column D1 , the first formula, second column E1 second formula, then select second column and using conditional formatting > new rule > use a formula... and enter D1>2 then format your colour. Hide column E.