Jon_JnP Posts: 6, Reputation: 1 New Member #1 Sep 4, 2013, 08:14 AM
Subtracting time - if the cell is not blank
I'm trying to have a cell tell me the difference in times. We record the start and end time of each job, and it would be handy to have it calculate it for me... currently I have two ways I can think of two do it, one of which works for the most part (just annoys me lol)...

=TEXT(C9-D8,"h:mm")

This one works, but if there is an end time, and it's the last run of the day, it gives a bad value for the next one... it works, but the bad value annoys me :-)

I want something like this to work...

=IF(OR(ISBLANK(D8),ISBLANK(C9)),"",C9-D8)

The problem here is that it considers the time as a decimal I think...

Any thoughts? - Thanks in advance!
 JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #2 Sep 5, 2013, 08:02 AM
If the cell will be EMPTY, then maybe this:

=IF(COUNT(C8,D9)=2, TEXT(C9-D8,"h:mm"), "")
 Jon_JnP Posts: 6, Reputation: 1 New Member #3 Sep 6, 2013, 09:31 AM
Works like a charm! You're awesome!

Thanks a million!
 JBeaucaire Posts: 5,426, Reputation: 997 Software Expert #4 Sep 6, 2013, 11:46 AM
If there's ever a chance the TIMES in those two cells will go past midnight, so you're trying to subtract 1:00am from 11:00pm, the formula would need to be a little longer:

=IF(COUNT(C8,D9)=2, TEXT(((C9-D8)+(D8>C9)),"h:mm"), "")

 Question Tools Search this Question Search this Question: Advanced Search

## Check out some similar questions!

Chargeing time for new Cell phone battery [ 1 Answers ]

Replaced vx4400 Li-ion dead battery with BTMLLL - 03613M new battery Li-on both are 3.7v. What is the charging time.

I have two heaters, both with carrier thermostats. One of them is failing to keep the time properly (time flashes) which of course messes up the programming. I swapped the two faceplates and had the same result in the first location with the other faceplate. Seems like the signal from the...