• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1555

# Excel 2010: Formula for time Calculation

Hello Experts!

I have a column that contains a date/time format of "m/dd/yyyy hh:mm" and is sorted from oldest to newest.   I'm looking for a formula that can take the time in the cell above and minus it from the time that is shown in the cell below (See snippet attached) and then place that value in column G.
Example.jpg
0
itsmevic
• 5
• 3
1 Solution

Commented:
Hi, itsmevic.

Assuming that there are no seconds in the times then the following formula shows the minutes between each pair of times...
=IF(A3="","",((A3-A2)*24*60))

Regards,
Brian
0

Author Commented:
Hi Red,

Thanks for your input.  Unfortunately it's not pulling how it should as it's Counting and not computing the time variance.  I've attached another example that I'm hoping will explain better what it is we are trying to accomplish.
Example.jpg
0

Commented:
itsmevic,

Edit: Formula changed and example added.

No, it's not counting. However, I suspect that your times do have seconds, so please try this...
=IF(ISNUMBER(A3),ROUND(ABS((A4-A3)*24*60),0),0)

Thanks,
Brian.Time-Difference.xlsm
0

Author Commented:
I've attached a redacted version, hopefully that will help.
0

Commented:
Thanks, itsmevic.

Please see attached. The formula is...
=IF(AND(ISNUMBER(E1),ISNUMBER(E2)),60*(HOUR(E2)-HOUR(E1))+MINUTE(E2)-MINUTE(E1),0)

I also changed the formatting to General.

(BTW, is it correct that the number of days has no influence?)

Regards,
0

Author Commented:
That is correct Brian.  Days wouldn't matter here because this particular report is pulled off the server in one hour increments, so the report would reflect that day.  Going to test the new formula now.  Thanks for the quick turn-around, your help is GREATLY APPRECIATED!
0

Commented:
itsmevic,

this particular report is pulled off the server in one hour increments
In that case the following formula is simpler....
=IF(AND(ISNUMBER(E1),ISNUMBER(E2)),(E2-E1)*24*60,0)
... (it's also safer on the off-chance that somehow more than one day's data is included).

Regards,
Brian.
0

Commented:
Thanks, itsmevic.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.