CODEWITHSUNDEEP
excelif-statementtime
Xupla
Xupla

Reputation: 203

If Statement to check time in excel

I need to create an if statement to check time in excel. For instance if time is between

00:00:01 to 06:00:00 I give a value of 1 if time is 06:00:01 to 12:00:00 I give a value of 2 if time is 12:00:01 to 18:00:00 I give a value of 3 and if time is 18:00:01 to 00:00:00 I give a value of 4

Any ideas how I should implement this?

Upvotes: 2

Views: 25831

Answers (3)

Jerry
Jerry

Reputation: 71538

Assuming that the time is in cell A1, you could use:

=CEILING(A1/0.25,1)

It worked on some sample data for me:

12:01:03        3
18:19:00        4
00:00:01        1

The above works because dates are actually numbers in Excel, and 1 unit represents 1 day. So naturally, if you divide 1 day by 4, you get what you are looking for.

Upvotes: 6

Alexander Bell
Alexander Bell

Reputation: 7918

You can use Excel Worksheet formula like shown below (it reads the system time using function NOW()):

=IF(HOUR(NOW())>18,4,IF(HOUR(NOW())>12,3,IF(HOUR(NOW())>6,2,1)))

If HOUR is already given in, for e.g., column A1, then use a simplified formula:

=IF(A1>18,4,IF(A1>12,3,IF(A1>6,2,1)))

Upvotes: 0

Santosh
Santosh

Reputation: 12353

Try this 

=TEXT(IF(INT(TEXT(A1,"[m]"))<=360,1,IF(INT(TEXT(A1,"[m]"))<=720,2,IF(INT(TEXT(A1,"[m]"))<=1080,3,IF(INT(TEXT(A1,"[m]"))<=1440,4,"")))),"#")

Upvotes: -1

Related Questions