Change time in Date Coulmn

[sathish_kumar1]

Hi folks,

 I have a table with a column named Lead Creation Date that represents a Datetime. Format -> 13-12-2021  05:04:56 .

Now when Lead creation is less than 10:00:00 I want to replace the time 04:50:00 with 10:00:00 and if it is more than 18:00:00 it should also become 10:00:00.

The Lead Creation Date should look like 13-12-2021 10:00:00 now.

Basically, Time Range between 10 to 18 should remain the same and the rest should be replaced with 10:00:00.

It would be nice if someone can help me.

Thanks

Tiwari

[tackytechtom · Accepted solution]

Hi @sathish_kumar1 ,

How about this:

I used the following formular and changed the type of the new column to date/time afterwards:

TomsNewDateTimeColumn = 
IF ( 
    HOUR(Table[TomsDateTimeColumn]) < 10 || HOUR(Table[TomsDateTimeColumn]) > 18, 
    Table[TomsDateTimeColumn].[Date] & " " & TIME ( 10, 0, 0 ), 
    Table[TomsDateTimeColumn].[Date] & " " & TIME ( HOUR(Table[TomsDateTimeColumn]), MINUTE(Table[TomsDateTimeColumn]), SECOND(Table[TomsDateTimeColumn]) )
)

Does this solve your issue? 🙂

/Tom

https://www.tackytech.blog/

https://www.instagram.com/tackytechtom/