Ask Experts Questions for FREE Help !
Ask
    Jon_JnP's Avatar
    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's Avatar
    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's Avatar
    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's Avatar
    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"), "")

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


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.

Carrier thermostat problem. Time flashes, unit goes blank [ 1 Answers ]

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...

Talking Time adjuster on cell phone [ 3 Answers ]

Could anybody tell how to Adjust talk time of one minutes on outgoing calls. on nokia cell phone (N82)?:confused::confused::confused::confused::confused:

Does DNA get smaller every time a cell divides [ 6 Answers ]

Hi guys, it is my understanding that DNA is composed on tandem repeats and interspersed repetative elements which account for over 90 percent ( I can't remember the exact number) of the DNA. This percent is basically junk DNA or non-coding DNA. Therefore every time a cell divides, specifically...


View more questions Search