IF statement for time

[ggzmorsh]

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 

Final Time = IF(TIME(Hour,00,00) < TIME(8,0,0),Date1 - 1,Date1)

but returned an error. TIME has the wrong data type or result is too large or too small.

[ggzmorsh]

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. 

[Anonymous]

@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

[az38 · Accepted solution]

Hi @ggzmorsh 

try 

Final Time = IF([Hour] < TIME(8,0,0),Date1 - 1,Date1)

or

Final Time = IF(TIME(VALUE(LEFT([Hour],2)),00,00) < TIME(8,0,0),Date1 - 1,Date1)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[ggzmorsh]

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. 

[az38]

@ggzmorsh mayb e your Hour field is set as Text. So, try my second statement or change Data type in power query editor mode

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

I used this:

FinalTime = IF('Table'[HOUR] < TIME(8;0;0);'Table'[DATE1]-1;'Table'[DATE1])

It works:

Please note that Month and Day are switched in date fields, because i use a different Locale. Thus this is calculated 1 day back from the 1st of may.