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

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
Asked:
itsmevic
  • 5
  • 3
1 Solution
 
redmondbCommented:
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
 
itsmevicAuthor 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
 
redmondbCommented:
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)

Please see attached.

Thanks,
Brian.Time-Difference.xlsm
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
itsmevicAuthor Commented:
I've attached a redacted version, hopefully that will help.
Example-Spreadsheet.xlsx
0
 
redmondbCommented:
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,
Brian.Example-Spreadsheet-V2.xlsx
0
 
itsmevicAuthor 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
 
redmondbCommented:
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
 
redmondbCommented:
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.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now