• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1547
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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