Adding negative times

[JRP1218]

Working on a spreadsheet to calculate on-time performance. I have a set time for a daily arrival and cells for the actual arrival each day.

EXAMPLE:

A B

C D

E

F

G

H

I

J

K

L

M

<tbody>
</tbody>

SCHEDULED PICKUP TIME

MONDAY

MINUTES LATE/EARLY

TUESDAY

MINUTES LATE/EARLY

WEDNESDAY

MINUTES LATE/EARLY

THURSDAY

MINUTES LATE/EARLY

FRIDAY

MINUTES LATE/EARLY

WEEK PERCENTAGE LATE/EARLY

WEEKLY ONTIME PERFORMANCE

<tbody>
</tbody>

7:30:00 AM

8:00:00 AM

-00:30

8:00:00 AM

-00:30

8:00:00 AM

-00:30

8:00:00 AM

-00:30

8:00:00 AM

-00:30

0:00

100.00%

<tbody>
</tbody>

The formula i'm using to calculate the "Minutes Late/Early" for each day is =IF(B4-C4<0, "-" & TEXT(ABS(B4-C4),"hh:mm"), B4-C4) My issue is adding the negative times in Column "L".

Any ideas so it will add the negative times and display a negative percentage?

 

[bewsh1987]

try


=IF(B4-C4<=0,TEXT(ABS(B4-C4),"-hh:mm"),TEXT(B4-C4,"hh:mm"))

 

[JRP1218]

Doesn't help. Not all number I am adding are negative. Is there a way to convert the (- time) to a number so it can be added?

 

[Jonmo1]

Negative Times actually do retain their negative values just fine for use in math operations.
The issue is that the cell cannot be FORMATTED as a time, it shows as ########.
But the actual value is still there.

I would suggest using a helper row
1 row that has the real numeric value of the times <- you can even hide this row
and a 2nd row to use a formula that converts them to a text value with the - to show negative.

Any math operations you need to do with these time values, use the 1st row.
The 2nd row is for Display.

 

[SpillerBD]

I think you can avoid the helper row. Where your results have to display the negative time just can't be used to source values for the final % calculation.

 

[JRP1218]

Ok, I figured this one out. Now I have another issue. I have to take the time difference sum and if it is within 15 min + or - the original window return a "0" in the cellif it is more that 15 minutes it returns the results. the formula i'm trying to use is =IF(AND(B8-C8>=0.25,B8-C8<0),0,B8-C8) where B8 is the fixed window time and C8 is the actual Arrival time. This formula returns the results of B8-C8 no matter what arrival time is entered. Any thoughts?

 

[SpillerBD]

I think you can use MROUND, but the multiple has to be the serial number value for 15 minutes, which is 0.0104166666666667