Comparing Dates Based on Weekdays

[Anonymous]

Dear Team,

I would like to script a conditional column to assess that this rule applies, no item created in a given week is getting approved on a date that comes after Monday 12:00 PM next week, meaning, that the approval date should never exceed the deadline of Monday 12:00 PM that comes directly following the week were the items got their creation date, any insights please?

[Vijay_A_Verma]

Please post some sample data as well as results expected. 

[Anonymous]

Well, I don't know what to post actually, they're just 2 columns here, both of dates, one is for the creation date and the other for the approval dates and all I need to make sure of that the approval dates are not coming after the deadline which is set to be every Monday at 12:00 PM. for each week.

[Vijay_A_Verma]

So will following formula do?

=[creation date]<=[approval date]

This will show False if creation date > approval date otherwise True.

[Anonymous]

Not really, ok, I’ll give an example, today is the start of the ISOWEEK #22, right? Ok, so, today at 12:00 PM is supposed to be the deadline for last week entries, so, any entries that were created at any point from Monday, 23-May-22 at 12:00 AM till today, 30-May-22 at 11:59 PM should have approval dates that are before today, 30-May-22 at 12:00 PM, otherwise, they are invalidly approved, and that is the conditional column that I need to check, it checks the entries of each week to make sure that this rule applies and provide an input by the end.

[Vijay_A_Verma]

Use this in a custom column

= if [Approval Date]<Date.AddDays(Date.EndOfWeek(Date.AddDays(Date.From([Creation Date]),-2)),2)&#time(12,0,0) then "Valid" else "Invalid"

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3NNc3MjAyUjA0tDIwUNIBiVhCRYxAIrE60RhiIFVGxpiq4GKGVqZoqoAiljBVphiqYG4wQJhlbICuClkEaFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Creation Date" = _t, #"Approval Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Creation Date", type datetime}, {"Approval Date", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Validation", each if [Approval Date]<Date.AddDays(Date.EndOfWeek(Date.AddDays(Date.From([Creation Date]),-2)),2)&#time(12,0,0) then "Valid" else "Invalid")
in
    #"Added Custom"