Comparing Dates Based on Weekdays

Question

Dear Team,&nbsp;&nbsp;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 · Answer

Use this in a custom column= if [Approval Date]&lt;Date.AddDays(Date.EndOfWeek(Date.AddDays(Date.From([Creation Date]),-2)),2)&amp;#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 testlet
    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]&lt;Date.AddDays(Date.EndOfWeek(Date.AddDays(Date.From([Creation Date]),-2)),2)&amp;#time(12,0,0) then "Valid" else "Invalid")
in
    #"Added Custom"

vijay_a_verma · Answer

Please post some sample data as well as results expected.&nbsp;

anonymous · Answer

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 · Answer

So will following formula do?=[creation date]&lt;=[approval date]This will show False if creation date &gt; approval date otherwise True.

anonymous · Answer

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.&nbsp;&nbsp;

Forum Discussion

Comparing Dates Based on Weekdays

5 Replies

Related content

Comparing Dates

Count of end dates compared to begin date

Trouble comparing dates with todays date

Calculate Weekdays between dates

Average $ per weekday