How to use the SUM formula to subtract and calculate time elapsed from mm/dd/yy hh:mm cell

How do I properly use the SUM formula to subtract and calculate time elapsed from mm/dd/yy hh:mm cells?

Example I'm trying to achieve:
Cell A1: 9/27/23 4:15 PM
Cell B1: 9/27/23 4:27 PM
Cell C1: =SUM(B1-A1) to show 00:12
Cell D1: =IF(D1>00:15,NO,YES) to show YES

I've been trying to throw different SUM formulas and custom formatting and this and that, but cannot for the life of me get it to work. I think Office365 web is partially the problem, but perhaps there is something at play.

During the process of trial and error, I've managed to get outputs to show "-4325.4"2 type output or "#######" with no actual numbers. I tried formatting the cell various ways and can't get it to work. Please help.

After getting the correct output going I'm going to throw an IF formula in D1 "=IF(C1>00:15,NO,YES)" to see what tickets are meeting SLA response times.

Update: In the process of adding a sample file, I got =SUM and =SUMPRODUCT formulas to work in two of the cells as samples, but the math is incorrect. Looking forward to learn something about this situation. Thanks a bunch.

The SUM() function is redundant in that formulation. You can just use =B1-A1 without the SUM() function.

I see no reason why you should be getting an error value or no display based on your description. What do you see when you format as general?

The usual problems:

One or both are text strings and Excel is not converting the text to numbers correctly.
B1 is smaller/earlier than A1, and Excel does not know how to display negative times (other spreadsheets are not limited in this way).
The number format is set to show more information than can fit in the width of the column. Make sure to use a "short" number format like [hh]:mm

If you can upload a sample file to the forum, we may be able to help in more detail.

When I recreate your data and format the result as h:mm in customized I get the expected results. I suspect without seeing your actual file that your data in columns A & B are text formatted and not true date time.

E6 looks correct to me. When you subtract 8:20:01 PM from 8:42:00 PM, you get 0:21:59 hours:minutes:seconds. Excel does not round to the nearest minute when you format a time value to only show minutes, so 0:21:59 formatted as hh:mm correctly displays as 00:21.

Do you need the calculation to round to the nearest minute? =MROUND(D6-C6,TIME(0,1,0))

Originally Posted by MrShorty

The SUM() function is redundant in that formulation. You can just use =B1-A1 without the SUM() function.

I see no reason why you should be getting an error value or no display based on your description. What do you see when you format as general?

The usual problems:

One or both are text strings and Excel is not converting the text to numbers correctly.
B1 is smaller/earlier than A1, and Excel does not know how to display negative times (other spreadsheets are not limited in this way).
The number format is set to show more information than can fit in the width of the column. Make sure to use a "short" number format like [hh]:mm

If you can upload a sample file to the forum, we may be able to help in more detail.

Originally Posted by alansidman

When I recreate your data and format the result as h:mm in customized I get the expected results. I suspect without seeing your actual file that your data in columns A & B are text formatted and not true date time.

Originally Posted by MrShorty

E6 looks correct to me. When you subtract 8:20:01 PM from 8:42:00 PM, you get 0:21:59 hours:minutes:seconds. Excel does not round to the nearest minute when you format a time value to only show minutes, so 0:21:59 formatted as hh:mm correctly displays as 00:21.
Book (1).xlsx
Do you need the calculation to round to the nearest minute? =MROUND(D6-C6,TIME(0,1,0))

Ok, I see now about the seconds. Formatting the date/time cells to show 00:00:00 it would indeed require rounding. After testing it and manually entering it I do see it works properly. =MROUND(D6-C6,TIME(0,1,0) does the subtraction and then rounding. Perfect.

Follow up question:
1) How would I get the =IF function to cooperate and populate in column F on the attached sample? I'm unable to get it to work ... =IF(E7>15,FALSE,TRUE) ... I tried =IF(E7>0:15,FALSE,TRUE), but that just throws an error.

E.g. Ideally I would like it to say YES if we respond under 0:15. If it takes us longer than 0:15 it is NO.

I've played around with the =IF function various ways and can't get it to work for me. They all just say TRUE or FALSE despite me adjusting. Cell E6 is 0:22 which is >15, so it should show as FALSE or NO, if a YES/NO output is an option. (I don't really need or want TRUE/FALSE)Book (1).xlsx

The conditional part of the function (E7>15) is comparing the actual value of E7 with the number 15. Note that the way the spreadsheets store date/time information, 15 means "15 days" or "15 Jan 1900," so your first comparison is asking if E7 is greater than 15 days or after 15 Jan 1900. All of the time values will be less than 1 day, so they will all be less than 15 days.

Probably the most reliable way to enter a value that means "15 minutes" into a formula like this is to use the TIME() function. =IF(E7>TIME(0,15,0),...). The TIME() function will return a value between 0 and 1 that accurately represents the combination of hours, minutes, and seconds that you enter.

Try

Formula:

Please Login or Register  to view this content.