SOLVED

date range calculations

Copper Contributor

SOLVED

G'day, all. I'm needing some help with calculating time between two date/time entries only if another cell in that row is filled, if not, leave blank. Date entry minus Date exit if RESULT column is filled, otherwise leave blank. Thank you

4 Replies

@Ding080 

Assuming that the duration will never be more than 31 days:

=IF(AND(W11<>"",W11<>"OPEN"),V11-B11,"")

Format as dd:hh:mm:ss

Thank you, What if the duration is longer than 31 days? is there a formula that will cover both scenarios?
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Ding080

The formula would have to return a text result:

 

=IF(AND(W11<>"",W11<>"OPEN"),INT(V11-B11)&":"&TEXT(V11-B11,"hh:mm:ss"),"")

excellent, thank you :)
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Ding080

The formula would have to return a text result:

 

=IF(AND(W11<>"",W11<>"OPEN"),INT(V11-B11)&":"&TEXT(V11-B11,"hh:mm:ss"),"")

View solution in original post