CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Viktor Krum
Viktor Krum

Reputation: 33

IF function with time

Picture of cells

(https://i.gyazo.com/ac6db30cccd2047df33560125a8177a1.png)

The cells content:

C1: 15:00
C2: 22:00
C1 and C2 are start time and end time on a work day.

and FYI for those not knowing what these numbers might mean
22:00 = 10pm
10:00 = 10am

My function on cell C3 should be following: If I am working between C1 and C2 then I want it to make it calculate from C3 the amount of hours from 19.00, in my case I have 22:00 in that cell so it should say 3 on C3 when I have C2= 22:00. How to do it?

Right now I have this simple function which I just tried in C3 =IF(C2=TIMEVALUE("22:00:00");3;0) and it does not seem to work, it says 0 on the cell, which means it does not really know that it says 22:00 on C2.

Upvotes: 1

Views: 8821

Answers (2)

Sebastian Rothbucher
Sebastian Rothbucher

Reputation: 1483

In order to use a fixed time in a formula, you could do =IF(C2=VALUE("22:00:00");"It's 10pm";"other time") - you actually got close, but it's VALUE I would try.

When you want to figure out how long after 7pm you've stayed in, try =(C2-VALUE("19:00:00"))*24 (and format the cell as number)

Upvotes: 1

Sam
Sam

Reputation: 497

Entering time inside the formula presents problems due to the formatting as the colon will make the formula think you want to do something else and quotes will change the format to text.

Here's a formula that works: D1=IF(AND(C1>A1,C1<B1),3,0) where A1 is your start time, B1 is your end time, C1 is whatever time you are testing.

To calculate the difference between current time C1 and the end of your work-day, then just change the 3 to SUM(C1,-B1) and format as a time value (HH:MM).

To add cuteness: change C1 to calculate the current time as HH:MM:SS so you can tell exactly how long until you go home from work: C1=TIMEVALUE(HOUR(NOW())&":"&MINUTE(NOW())&":"&SECOND(NOW())) (NOTE: there's probably a more elegant way to do this - I'll post later if I figure it out)

Upvotes: 0

Related Questions