Formulas and blank cells

[kaldrazidrim]

Sorry, I'm sure it's been asked before...

I am using a time stamp in B2 to clock in, and a time stamp in C2 to clock out, and the following in D2 to calculate elapsed time:
(every time I post my formula it gets truncated) =IF(C2<b2,c2+1,c2)-b2><b2,c2+1,c2)-b2">

Here it is with spaces added so it doesn't get truncated. =IF(C2 < B2,C2+1,C2)-B2
<b2,c2+1,c2)-b2><b2,c2+1,c2)-b2><b2,c2+1,c2)-b2>
Right now when I copy this formula all the way down column D, it show zeros in each cell of column D. I need the cells in D to be (or appear) blank until there is actually data in column B and C, instead of showing zeros.

Thanks in advance, this community has been so helpful!</b2,c2+1,c2)-b2></b2,c2+1,c2)-b2></b2,c2+1,c2)-b2></b2,c2+1,c2)-b2"></b2,c2+1,c2)-b2>

 

[VoG]

Your formula didn't post correctly because the board software interprets < as an HTML tag. Try reposting it with spaces around <

 

[kaldrazidrim]

Got it. Thanks! Edited.

 

[VoG]

Try

=IF(OR(B2="",C2=""),"",IF(C2< B2,C2+1,C2-B2))<b2,c2+1,c2-b2))></b2,c2+1,c2-b2))>

 

[kaldrazidrim]

Try

=IF(OR(B2="",C2=""),"",IF(C2< B2,C2+1,C2-B2))<b2,c2+1,c2-b2))></b2,c2+1,c2-b2))>

Hey VoG, that does not seem to work. The formula shows up in the cell and just sits there. It does not return a result.

 

[VoG]

Make sure that the cell is formatted as General then re-enter the formula.

 

[Scott Huish]

=if(count(b2:c2)=2,if(c2< b2,c2+1,c2-b2),"")

 

[kaldrazidrim]

Make sure that the cell is formatted as General then re-enter the formula.

Works! Thank you so much! One more quick question. If my time stamp is within the same minute, D returns a value of 0:00. I would like it to default to a minimum elapsed time of one minute 0:01.

For example, currently B= 2:10 AM C= 2:10 AM D= 0:00

I would like it to return this: B= 2:10 AM C= 2:10 AM D= 0:01

What do you think?

 

[VoG]

Possibly

=IF(OR(B2="",C2=""),"",MIN(IF(C2< B2,C2+1,C2-B2),1/1440))<b2,c2+1,c2-b2),1 1440))=""></b2,c2+1,c2-b2),1>

 

[kaldrazidrim]

.