Forum Discussion
IF statement for time
I need an IF statement where if a specific column has a Date and Hour it would get me the Final Date by checking a separate hour table or a condition that if the Hour is between 00:00:00 and 08:00:00 it would return the previous date.
An example table below shows that ID A to D has an hour between 00:00:00 and 08:00:00 and it must return Date 1 which is reduced by 1 and if the condition is false simply return Date1.
| ID | Date 1 | Hour | Final Date |
| a | 01/05/2020 | 01:00:00 | 01/04/2020 |
| b | 01/05/2020 | 02:00:00 | 01/04/2020 |
| c | 01/05/2020 | 03:00:00 | 01/04/2020 |
| d | 01/05/2020 | 04:00:00 | 01/04/2020 |
| e | 01/05/2020 | 11:00:00 | 01/05/2020 |
The formula i tried to use is
6 Replies
I forgot to mention this:
Final Time = IF([Hour] < TIME(8,0,0),Date1 - 1,Date1)
When i used this formula earlier it would always return as true.
I do not know why my data is always returning the statement true. So the workaround i did is i extracted just the HOUR from the Hour column and used the IF statement on that and it worked.
ggzmorsh
The issue is possibly cause by unmatched format, please check the format for the date(Date/ Date time) and hour(Time) columns.Best,
Paul
