Is there an efficient way to do this excel function

0 votes

I wanted to divide a trip's departure time into 4 separate "times of day" in Excel (Morning, afternoon, evening, Night)

This script is mine, and the start time column contains the 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 easier.

Feb 7, 2023 in Others by Kithuzzz
• 38,020 points
405 views

1 answer to this question.

0 votes

Use vlookup():

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

enter image description here

answered Feb 7, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,720 points
625 views
0 votes
1 answer

Is there an excel function to tell if a cell has a dependency?

I think there is no built-in function ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,720 points
808 views
0 votes
1 answer

Is there a way in Microsoft Excel to give specific bins different bin widths when making a histogram plot?

Excel built-in histogram tool only allows equal ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,720 points
1,860 views
0 votes
1 answer

Is there a way to hide excel formula using Apache POI

In Excel, hiding formulae is a feature ...READ MORE

answered Oct 11, 2022 in Others by narikkadan
• 63,720 points
1,136 views
0 votes
1 answer

Date format conversion in Excel

You appear to be misunderstanding the point: ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,720 points
452 views
0 votes
1 answer

Excel - How do I round a date type to the next hour if it is more than one minute

Add almost 30 minutes and it'll get ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,720 points
330 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,654 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,899 views
0 votes
1 answer

Is there a way to lock cells after editing an excel sheet that's on sharepoint?

The Excel Web version (which, based on ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,344 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,720 points
1,959 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP