Nested if functions using time values [Closed]

Posts
1
Registration date
Wednesday August 10, 2016
Status
Member
Last seen
August 10, 2016
- - Latest reply: 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.
See more 

2 replies

Posts
1833
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 30, 2018
130
0
Thank you
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
Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
1
0
Thank you
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