need help asap...calculating difference between 2 times

[flipmagoo]

hello...please help. Ive tried every formula out there.

example

columns b and c have time values in them pulling from a index\match formula and pivot table. Time values format is x:xx AM\PM. In column A i want the difference of time between columns b and c. This is where i get snagged up. Column B or C may have a higher value than the other throughout the spreadsheet. Example..... col B row1 7:00 am - col C row1 7:30 am Col A row-1 0:30 difference. Now the next row of times maybe col B row2 7:30 am - col C row2 7:00 am Col A row-2 ##############. I understand why this happens just cant find a formula to correct this. I need the formula to first find the larger value in the 2 columns and subtract the higher time value from lesser time value to get the difference. Can be done and how?

Mr Magoo

[benishiryo]

hi flipmagoo, welcome to the forum. try:
=ABS(A1-B1)

Absolute makes it always positive

[flipmagoo]

no go tried that formula already...tried =if....abs...int...min\max..><=...i tried everything. wish it was that simple. Excel and time doesnt work well together.

[djapigo]

benishiryo's formula should work...

Date and times are simply numbers formatted to look like a date or time... dates are integers while times are a fraction of a day... so if you remove the time format, you should be a decimal representation... 6am = .25, 12pm = 0.5, 6pm = .75 etc.

So your two times can be subtracted and can result with a negative number. The ABS function will simply remove any negatives.

If it still doesn't work, can you post a sample workbook for us to look at. (click on Go Advanced and look for a paperclip).

Thanks,
Dennis

[flipmagoo]

This is what I am talkin about. - I need a formula that will take the higher time value in column B and C and subtract it from the lesser time value. At any given moment column B or C will have the higher value. ABS doesnt work, MOD, IF...I cant find anything that works with time that will do what I need it too.
Excel Issue.jpg

[dredwolf]

Try this:

Please Login or Register  to view this content.

NOTE - This will will strip away the date portion of the Date-time code, so if the dates of the times are different, it will NOT calculate the true time difference between them..

Hope this helps

Edit-
the reason for the note is I suspect that is what has happened in the last row on your screen shot, the two dates are different, so it calculated the ACTUAL time difference between the 2 cells...
Which may be what you actually wanted, My formula only calculates the difference between the Times, ignoring the Dates