SOLVED

# date range calculations

Copper Contributor

# date range calculations

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

# Re: date range calculations

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

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

Format as dd:hh:mm:ss

# Re: date range calculations

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

# Re: date range calculations

The formula would have to return a text result:

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

# Re: date range calculations

excellent, thank you :)
1 best response

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

# Re: date range calculations

The formula would have to return a text result:

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