Solved

Excel 2010:  Formula for time Calculation

Posted on 2012-08-29
1,488 Views
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
Question by:itsmevic

LVL 26

Expert Comment

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 Comment

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

LVL 26

Expert Comment

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 Comment

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

LVL 26

Expert Comment

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 Comment

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

LVL 26

Accepted Solution

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

LVL 26

Expert Comment

Thanks, itsmevic.
0

Featured Post

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…