Time subtraction & conditional formatting in Google Sheets
I'm working on a simple 3-column table for logging sleep hours. It's based on "went to bed" time (col 1), "woke up" time (col 2) and "time in bed" (col 3).
All 3 columns are formatted as - h:mm - and Google seems to calculate the time difference correctly, considering that it has to subtract "backwards", as in "woke up" time minus "went to bed" time.
The problem arises when I add conditional formatting to the "time in bed" column. What I want is the hours spent in bed in green if >= 8:00:00 or in orange if < 8:00:00.
Adding the date as well to the "went to bed" time and "woke up" time didn't help.
The ABS function on the difference didn't help either.
When "time in bed" is >= 8:00:00 AND "went to bed" time happens to be BEFORE midnight, I see an orange number, where I should see a green one
Every other case works as expected.
custom formula for green color:
=IF(TIMEVALUE(D4)>=0.33,1)
- How to get the today's and tomorrow's date in swift 4
- HTML time tag for duration
- How can I get the Windows system time with millisecond resolution?
- Android: How to get values from date and time picker
- How do I get a large integer value from the REALTIME operator in postscript? miliseconds?
- Gitlab CI Display times in job log
- JS how to add hours and minutes to scheduled date
- how to find AVG of time in snowflake
- c++ std::chrono implicit conversion standard method
- Getting current date and time in JavaScript
- Surpassing MySQL's TIME value limit of 838:59:59
- Format std::time output
- Fast way to get the current Calendar object in Java
- Difference between time.sleep() and threading.timer() accuracy and efficiency in Python
- Regarding the pytz module and the time module in Python
- Display a countdown for the python sleep function
- Automatically shows date and time in VBA on multiple columns
- How to convert a Date to the midday Time in UTC in Ruby-on-Rails, regardless of the timezone?
- How can I handle a leap second correctly in my application
- How to set 1 second time delay at assembly language 8086
- How to format current time using a yyyyMMddHHmmss format?
- Specifying a time span / duration constant in Google Sheets formula over 24h
- How to get the current Time
- Strange timestamp using calendar
- How can I get the current time (now) in UTC?
- What is the significance of January 1, 1601?
- Displaying the build date
- Get date ISO string without conversion to UTC timezone
- AWS InvalidSignatureException, Signature expired when running from docker container
- Remove seconds from time in R