Forum Discussion

SSJ011's avatar
SSJ011
Frequent Visitor
5 years ago

Create Calculated column to get the correct date

Hi, 

 

Want to get the correct date from the completion date and Time with one condition.

 

if the date & time is before 6:30 AM in the Completed column then the calculated column should take yesterday's date else date updated in the

Completed DateCalculated Column Result
11/20/2020 06:29:0011/19/2020
11/20/2020 06:31:0011/20/2020

 

Thanks in advance for your assistance.

 

Regards

Sachin Jagdale   

2 Replies

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    5 years ago

    SSJ011 ,

     

    Try this measure:

     

    Column = 
    IF(
        TIME(HOUR('Table'[Date]), MINUTE([Date]), SECOND([Date])) < TIME(6, 30, 00),
        DATEVALUE([Date] - 1),
        DATEVALUE([Date])
    )

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello,

     

    You can use this conditional in a calculated column to get the correct date:

     

    =IF(TIME(HOUR([Completed Date]),MINUTE([Completed Date]),SECOND([Completed Date]))<TIME(6,30,0),[Completed Date]-1,[Completed Date])

     

    And if you want the correct date to have no time, you could use this:

     

    =IF(TIME(HOUR([Completed Date]),MINUTE([Completed Date]),SECOND([Completed Date]))<TIME(6,30,0),[Completed Date]-1-TIME(HOUR([Completed Date]),MINUTE([Completed Date]),[Completed Date]-TIME(HOUR([Completed Date]),MINUTE([Completed Date]))

     

    Regards

     

    Jesus