adjust if formula to look at relative date in date/time

[AI14]

Hi

how can i make my date of my date/time column relative to today?

i want to avoid going into my data everyday and changing the date

= Table.AddColumn(#"Filtered Rows", "AM/PM", each if [ST] <= #datetime(2023, 1, 20, 13, 50, 0) then "AM" else "PM")

ST
21/01/2023 04:30:00		AM
21/01/2023 20:30:00		PM
21/01/2023 06:30:00		AM
21/01/2023 14:30:00		PM
21/01/2023 13:30:00		AM

[ValtteriN]

Hi,

Try using DateTime.LocalNow() function.

E.g.

= Table.AddColumn(#"Replaced Value", "AM/PM", each if [ST] <= DateTime.LocalNow() then "AM" else "PM")

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[AI14]

hi,

thanks for reply.

i need the time 13:50 to be fixed - AM will always be below 13:50 and PM after.

kr

[ValtteriN · Accepted solution]

Hi,

In that case you can add the time like this: 

newDate = Date.From(DateTime.LocalNow()) & Time.FromText("13:50:00pm"),

next_step = Table.AddColumn(#"Replaced Value", "AM/PM", each if [ST] <= newDate then "AM" else "PM")