Create Calculated column to get the correct date

[SSJ011]

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 Date	Calculated Column Result
11/20/2020 06:29:00	11/19/2020
11/20/2020 06:31:00	11/20/2020

Thanks in advance for your assistance.

Regards

Sachin Jagdale   

[Anonymous]

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

[camargos88]

@SSJ011 ,

Try this measure:

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