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
fdibbins Posts 33 Registration date Sunday June 19, 2016 Status Contributor Last seen November 20, 2016 - Aug 16, 2016 at 10:47 PM
Related:
- Which of the following nested if functions are correct
- How many if statements can you nest - Guide
- What are the key functions of the supportassist for home pcs? - Download - Cleaning and optimization
- Accessor and mutator functions c++ - Guide
- Functions of spreadsheet - Guide
- Function of insert key - Guide
2 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Aug 10, 2016 at 12:17 PM
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
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
fdibbins
Posts
33
Registration date
Sunday June 19, 2016
Status
Contributor
Last seen
November 20, 2016
1
Aug 16, 2016 at 10:47 PM
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
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
