Nested if functions using time values
Closed
Dannnnn0710
Posts
1
Registration date
Wednesday 10 August 2016
Status
Member
Last seen
10 August 2016
-
10 Aug 2016 à 00:16
fdibbins Posts 33 Registration date Sunday 19 June 2016 Status Contributor Last seen 20 November 2016 - 16 Aug 2016 à 22:47
fdibbins Posts 33 Registration date Sunday 19 June 2016 Status Contributor Last seen 20 November 2016 - 16 Aug 2016 à 22:47
Related:
- Which of the following nested if functions are correct
- How many if functions can be nested in excel - Guide
- What is the function of a hard drive - Guide
- Functions of wifi - Guide
- Function of network card - Guide
- Spreadsheet functions - Guide
2 responses
Mazzaropi
Posts
1983
Registration date
Monday 16 August 2010
Status
Contributor
Last seen
24 May 2023
147
10 Aug 2016 à 12:17
10 Aug 2016 à 12:17
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 19 June 2016
Status
Contributor
Last seen
20 November 2016
1
16 Aug 2016 à 22:47
16 Aug 2016 à 22:47
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
