Working out Time difference

ads_1471

New Member
Joined
Apr 19, 2016
Messages
32
I have the below that I am trying to work out.

People have 15:00 minute breaks at present. I have data of how long people have taken. So want to know the difference in time against 15 minutes.

Person 1 - 9:53
Person 2 - 20:24
Person 3 - 15:00
Person 4 - 12:30

And so on. At present I get ##### for when someone is below 15:00.

Please help as driving me nuts
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
581
Office Version
365, 2013
Platform
Windows
If you go to File > Options > Advanced and check "Use 1904 date system" then all negative time values should be now displayed correctly:

1578322037308.png


Otherwise you'd need to use some sort of conditional formula, like:
=IF(A1>TIME(0,15,0),TEXT(ABS(TIME(0,15,0)-A1),"-hh:mm:ss"),TIME(0,15,0)-A1)

Hope it works for you.
 

Forum statistics

Threads
1,089,635
Messages
5,409,453
Members
403,264
Latest member
naturally_data

This Week's Hot Topics

Top