Nested if functions using time values

Closed
Dannnnn0710 Posts 1 Registration date Wednesday August 10, 2016 Status Member Last seen August 10, 2016 - Aug 10, 2016 at 12:16 AM
fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 - Aug 16, 2016 at 10:47 PM
Hello,
I have an excel formula here, but it wont work.

=IF(AND(G2>=TIMEVALUE("06:00:00"))*(G2<=TIMEVALUE("23:59:59")),"Delivery next day 5pm",IF(AND(G2>=TIMEVALUE("00:00:01"))*(G2<=TIMEVALUE("05:59:59")),"Delivery next day 12NN",""))

The formula itself should be correct, the thing is it doesn't show the correct result.


For this case, the correct result should be showing Delivery next day 12NN, but it shows blank.

Can someone tell me where did I get wrong?
Thanks a lot.
Related:

2 replies

Mazzaropi Posts 1965 Registration date Monday August 16, 2010 Status Contributor Last seen September 18, 2022 146
Aug 10, 2016 at 12:17 PM
Dannnnn0710, Good afternoon.

If the result of cell H2 is obtained by the formula:
=TIME(HOUR(G2),MINUTE(G2),SECOND(G2)), you can do:

I2 -->
=IF(AND(H2>=TIMEVALUE("06:00:00"),H2<=TIMEVALUE("00:00:00")),"Delivery next day 5pm", IF(AND(H2>=TIMEVALUE("00:00:00"), H2<=TIMEVALUE("06:00:00")),"Delivery next day 12NN",""))

Is this what you want?
Please, tell us if it worked for you.

I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 1
Aug 16, 2016 at 10:47 PM
Perhaps this?
Perhaps this?
=IF(AND(G2>=0.25,G2<=1),"Delivery next day 5pm","Delivery next day 12NN")

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Tue 16 Aug 2016) is actually 42598

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
0