Introduction
If you want to do calculations based on cells containing times you need to use the TIME and TIMEVALUE functions.
Add 1 hour to time
You can’t simply use =A1+1 to add an hour to a time stored in A1, you need to define which part of the time value – hh:mm:ss – you want to add one to. You must define the value as TIME then use hh,mm,ss options for the value to add
A | B | < B function > | |
---|---|---|---|
1 | 04:00 | 05:00 | =A1+TIME(1,0,0) |
2 | 04:00 | 04:30 | =A2+TIME(0,30,0) |
Show a time range using a cells time as starting point
Use CONCATENATE or & to add free text (“~”) between the time from an existing cell and existing cell +3 hours
A | B | < B function > | |
---|---|---|---|
1 | 04:00 | 04:00~07:00 | =CONCATENATE(TEXT(A1,”hh:mm”),”~”,TEXT(A1+TIME(3,0,0),”hh:mm”)) |
2 | 04:00 | 04:00~07:00 | =TEXT(A2,”hh:mm”)&”~”&TEXT(A2+TIME(3,0,0),”hh:mm”) |
If time in cell is less than defined time minus one day
If you want to use time from a reference cell in a function you have to use TIMEVALUE
e.g. if time in column A cell is less than 01:00, take date from column B cell and -1 day, else leave same value as column B cell
A | B | C | < C function > | |
---|---|---|---|---|
1 | 12:30 AM | 10/06/2016 | 09/06/2016 | =IF(A1>=TIMEVALUE(“01:00 AM”),B1,B1-1) |
2 | 02:00 AM | 10/06/2016 | 10/06/2016 | =IF(A2>=TIMEVALUE(“01:00 AM”),B2,B2-1) |
3 | 01:00 AM | 10/06/2016 | 10/06/2016 | =IF(A3>=TIMEVALUE(“01:00 AM”),B3,B3-1) |
Also see
Leave a Reply