# Using TimeValue function to display hours and minutes spent.

Copper Contributor

# Using TimeValue function to display hours and minutes spent.

Hi,

I am stuck with the following issue,

The above link has a spreadsheet where cell O10 and P10 have start time and end time respectively. I am trying to get hours shown in Q10 and remaining minutes (if any) in R10.

Q10 has to show blank if either of O10 or P10 is blank or if no whole hours has passed.

R10 has to show blank if either of O10 or P10 is blank and show remaining remaining minutes or elapsed minutes if any.

The spreadsheet shows examples but I dont want to see 0 or negatives as a result. 0 must be shown as blank.

We can assume P10 will always be greater than O10 so no need to check if its greater or not. (I will correct it if i enter wrong value for O10 or P10 when i see an error in either Q10 or R10). So the formula in Q10 and R10 has to be perfect.

Formulas already present Q and R cells have been made by brainstorming for few hours. I would like to receive better suggestions or options to acquire the desired result. Any help is much appreciated. <3

3 Replies

# Re: Using TimeValue function to display hours and minutes spent.

Hello ¿what are you doing in app?

# Re: Using TimeValue function to display hours and minutes spent.

I have modified the formula you had, to get the desired results. pls check:

PASTE IN Q8

=IF(OR(O8="",P8=""),"",IF(INT((TIMEVALUE(TEXT(P8,"hh:mm AM/PM"))-TIMEVALUE(TEXT(O8,"hh:mm AM/PM")))*24)=0,"",INT((TIMEVALUE(TEXT(P8,"hh:mm AM/PM"))-TIMEVALUE(TEXT(O8,"hh:mm AM/PM")))*24)))

PASTE IN R10

=IF(OR(O8="",P8=""),"",IF(INT((TIMEVALUE(TEXT(P8,"hh:mm AM/PM"))-TIMEVALUE(TEXT(O8,"hh:mm AM/PM")))*24*60)=0,"",IF(INT((TIMEVALUE(TEXT(P8,"hh:mm AM/PM"))-TIMEVALUE(TEXT(O8,"hh:mm AM/PM")))*(24*60)-(Q8*60))<0,"",INT((TIMEVALUE(TEXT(P8,"hh:mm AM/PM"))-TIMEVALUE(TEXT(O8,"hh:mm AM/PM")))*(24*60)-(Q8*60)))))

# Re: Using TimeValue function to display hours and minutes spent.

If this is what you want, it really is very simple.

Formulas:

Q9: =IF(OR(O9="",P9=""), "", IF(HOUR(P9-O9)=0, "", HOUR(P9-O9)))

R9: =IF(OR(O9="",P9=""), "", MINUTE(P9-O9))

Copy Q9:R9 into Q10:R13

Alternative:

Q9: =IF(OR(O9="",P9=""), "", HOUR(P9-O9))

with the following Custom format:

[=0]""; General

However, note that the value in Q13 will be zero, even if it appears to be blank.