CODEWITHSUNDEEP
exceldatetimeexcel-formula
Swapster
Swapster

Reputation: 13

Is there an efficient way to do this excel function

In excel I wanted to differentiate the starting time of a trip into 4 'times of day' (Morning, afternoon, evening, Night)

I made this script, the start_time column consists of time (hh:mm:ss)

=IF(
    AND([@[start_time]] >= TIME(20,0,0),
        [@[start_time]] <= TIME(23,59,59)
    ),
    "Night",
    IF(
        AND([@[start_time]] >= TIME(0,0,0), 
            [@[start_time]] < TIME(6,0,0)
        ),
        "Night",
        IF(
            AND([@[start_time]] >= TIME(6,0,0), 
                [@[start_time]] < TIME(12,0,0)
            ),
            "Morning",
            IF(
                AND([@[start_time]] >= TIME(12,0,0), 
                    [@[start_time]] < TIME(16,0,0)
                ),
                "Afternoon", 
                "Evening"
            )
        )
    )
)

I was wondering if there is any way to improve this function or make it more easy Thanks in advance!

Upvotes: 1

Views: 85

Answers (3)

Gravitate
Gravitate

Reputation: 3064

Firstly, you can significantly simplify your IF statement by removing the ANDs:

=IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(6,0,0), "Night",
  IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(12,0,0), "Morning",
    IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(16,0,0), "Afternoon",
      IF([@[Start Time]]-ROUNDDOWN([@[Start Time]],0) < TIME(20,0,0), "Evening", "Night")
    )
  )

The -ROUNDDOWN([@[Start Time]],0) here, allows you to enter a time with or without a date. It is not needed if your times do not also have a date attached.

enter image description here

Using a lookup table instead

The benefit of using a lookup table is that you can easily see and change the cut off times in a single place, rather than searching through multiple formulas.

For Lookups, you should NEVER use VLOOKUP. INDEX/MATCH is better in every way. It is more efficient, less fragile, more versatile, and arguably easier to use, once you are familiar with it.

enter image description here

=INDEX(RefTbl[Times Of Day],MATCH([@[Start Time]]-ROUNDDOWN([@[Start Time]],0),RefTbl[End Time],-1))

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8375

So, using vlookup() as suggested:

VLOOKUP(E2,$B$4:$C$8,2,1)

enter image description here

The advantage I find is that the times and the text are easily edited.

Upvotes: 1

Dominique
Dominique

Reputation: 17493

Recent Excel version has two functions you might use for this: switch() handles the multiple IF() and LET() gives you the opportunity to create a temporary variable inside your formula. (I would have put this in a comment, but two large URLs make this comment too large)

Upvotes: 0

Related Questions