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>

 

[kaldrazidrim]

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]

.

That does not quite work as expected. Of A and B are the same, it returns a result of 0:00 (should be 0:01) A is not equal to B, it returns a result of 0:01 no matter how much time has elapsed.

Thanks

 

[VoG]

Sorry, change MIN to MAX.

 

[kaldrazidrim]

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>

That does not quite work as expected. Of A and B are the same, it returns a result of 0:00 (should be 0:01) A is not equal to B, it returns a result of 0:01 no matter how much time has elapsed.

Also, the forum is truncating your formulas when they contain < (When I reply with . it shows the whole thing. That's the reasons for my funky replies.)

Thanks

 

[kaldrazidrim]

sorry, change min to max.

perfect!!! Thank you!!!

 

[Nando38]

I'm trying to insert times I have recorded to see if how long Godzilla's in a movie affects the rating out of 10 the movie gets(don't ask), but every time I put a time in (12min 57sec --> 00:12:57) it thinks the zeros I put in for the hour slot mean 12 for some reason and it also puts AM at the end (example: 00:12:57 --> 12:12:57 AM). What do I do?!

 

[VoG]

What do I do?!

Start your own new thread