Creating a string from time range

Question

Hi, 

I have to create a string variable for a range of time during the day:

02:00:00 - 05:59:00 is to be coded as "Early"

6:00:00 - 09:59:00 is to be coded as Morning

10 - 13:59 is to be coded as Noon

14 - 17:59 is to be coded as Afternoon

18- 21:59 is to be coded as Evening 

And finally 22:00:00 - 1:59 is to be coded as Night. 

Please help! (With full formula as I am relatively new to using IF statements with time values)

Thanks in advance.

Answer 1

Hi,

If will be a lengthy formula.  I have shown a screenshot as well.  Please retry.  If it still does not help, then upload your workbook to OneDrive/Google Drive and share the download link here.

Answer 2

Hi Ashish,

Is there any way to make it simple. I am relatively new to solving such things on excel and have been using if statements in the past for such cases. Unfortunately I am getting the syntax wrong for this particular problem.

Do you think, there is an If, Ifs statement that can work to solve this problem.

thanks for your help!

Answer 3

Hi,

If you have 1.1 million rows, you exceed the Excel row limit.  So, you can still get the correct result using Power Query.  However, sine the number of rows cannot be accommodated in a worksheet, we will have to transfer the result directly to the PowerPivot.

Answer 4

Is there any other way since my dataset has 1.1 million entries and Vlookup wont work

Answer 5

Hi,

The formula in cell B10 is

=IF(A10<=TIME(1,59,0),"Night",VLOOKUP(A10,$A$2:$B$7,2,1))

Here's a screenshot