Forum Discussion

ReliableBoy2's avatar
ReliableBoy2
Copper Contributor
Apr 07, 2023

Using TimeValue function to display hours and minutes spent.

Hi,

 

I am stuck with the following issue,

 

https://docs.google.com/spreadsheets/d/1TK1YrYzYgD-Sqgf4PmycPKbZ-iJH7ppD/edit?usp=share_link&ouid=101514595417469444876&rtpof=true&sd=true

 

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. ❤️

excel
Excel for web
Formulas and Functions
  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor
    Apr 08, 2023

    ReliableBoy2 

     

    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.

  • SAM_XL's avatar
    SAM_XL
    Copper Contributor
    Apr 08, 2023

    ReliableBoy2 

    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)))))

Resources