IF statement for dates

[Anonymous]

Good morning

I have a table with a Start Date/Time column and and End Date/Time column. There is also a Period Start Date/Time and Period End Date/Time columns. I want to try and add another column using a IF statement.

I want to show if either the Start Date/Time or the End Date/Time starts or ends or fall into  the Period Start Date/Time and End Period Date/Time

I have manged with the formula below to see if the Start or End is in the period but not the part if it falls into it. below is the result I get. I need the second row to populate with Yes also.

Example 

Start Date/Time                    End Date/Time                Period Start Date/Time            Period End Date/Time         In Production

01/01/2019 04.15:00            01/01/2019 06.15:00        01/01/2019 04.00:00                 01/01/2019 04.59:59              YES

01/01/2019 04.15:00            01/01/2019 06.15:00        01/01/2019 05.00:00                 01/01/2019 05.59:59              NO

01/01/2019 04.15:00            01/01/2019 06.15:00        01/01/2019 06.00:00                 01/01/2019 06.59:59              YES

In Production = IF(OR(TABLE1[UTC_GAME_START] >= TABLE1[Start Of Period Date/Time]
&& TABLE1[UTC_GAME_START]<= TABLE1[End Of Period Date/Time],
TABLE1[MATCH_END_TIME]>=TABLE1[Start Of Period Date/Time]
&& TABLE1[MATCH_END_TIME]<=TABLE1[End Of Period Date/Time]), "YES", "NO")

Thanks in advance

[v-piga-msft]

Hi @Anonymous ,

I still have a little confused about your logic.

It seems that the result is correct based on your logic.

1/1/2019 4:15:00 is not between  01/01/2019 05.00:00  and 01/01/2019 05.59:59 and 01/01/2019 06.15:00 is not between 01/01/2019 05.00:00  and 01/01/2019 05.59:59 either.

If it is convenient, could you describe your logic in more details so that we could help further on it?

Best  Regards,

Cherry

[Anonymous]

Hi Cherry

Thanks for getting back to me.

I want to show in running. Start is 04:15, So that populates the 4:00:00 - 4:59:59 with Yes, the "Event" is still in running between 5:00:00 and 5:59:59. So that populates 5:00:00 and 5:59:59 with Yes. The End Date/Time is 6:15:00 so that populates 6:00:00 - 6:59:59 with Yes

I hope that explains properly

Thanks again

Joe

[Anonymous · Accepted solution]

@v-piga-msft 

I have solved it

In Production = IF([UTC_END_TIME] > [Start Of Period Date/Time] && [UTC_START_TIME] < [End Of Period Date/Time], "YES", "NO")

Thanks for your time