Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

IF statement for dates

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
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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