cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brij
Helper I
Helper I

How to add number of days with a static hour value from a datetime column in power query?

Hi,

I have a column DateTimeStart (Example: 19/09/2022 7:15:00 PM) and I want to move this date to the next business day with the hour component being 8:00:00 AM (static value) whenever a time is greater than 5:00:00 PM to this resulting at 20/09/2022 8:00:00 AM. Is this possible?

 

Any help is appreciated.

 

Thank you,

Brijesh

8 REPLIES 8
jbwtp
Solution Sage
Solution Sage

Hi @Brij,

 

Something like this:

if Time.Hour(DateTime.From("05/09/2022 7:15:00 pm")) > 17 then Date.EndOfDay(DateTime.From("05/09/2022 7:15:00 pm")) + #duration(0,8,0,0) else DateTime.From("05/09/2022 7:15:00 pm")

replace DateTime.From("05/09/2022 7:15:00 pm") with a reference to a column e.g. [DateTimeStart]

 

Kind regards,

John

Thanks for the prompt reply.

I tried this solution but it goes to the next day only! See the screenshot below;

Brij_0-1663655666373.png

Column on the right is a custom column with the code provided. 26th Aug 2022 is Friday so I need it to be resulting in 29th Aug 2022 8:00:00 AM!

 

Please see if we can revise this code?

Thank you

Would this take care of weekends as well?

jbwtp
Solution Sage
Solution Sage

Hi @Brij,

 

Sorry, mu mistake.

This is the code that takes care of weekends too:

let
    Source = List.DateTimes(#datetime(2022,9,2,0,0,0), 30, #duration(0,4,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "DW", each Date.DayOfWeekName([Column1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "timeOffset", each
   let 
      timeOffset = [Column1] - #duration(0,8,0,0), // makes 8 AM to be a start of the day
      shiftAfterHoursToNextDay = if Time.Hour(timeOffset) > 8 then Date.StartOfDay(timeOffset) + #duration(1,0,0,0) else timeOffset,
      isWeekend = List.Max({0, Date.DayOfWeek(shiftAfterHoursToNextDay)-4}),
      res = shiftAfterHoursToNextDay + #duration(if isWeekend > 0 then 2 / isWeekend else 0, 8,0,0)
   in res
)
in
    #"Added Custom1"

This is a bit lengthy, but I guess you also want to take care of something like 05/09/2022 7:15:00 AM moving to 8 am. I tried to optimize it, but one way or another it does not look short and this is in my view probably the cleanest version.

 

Kind regards,

John

Thanks for your effort so far.

 

I think I did not explain my problem and desired result properly!

 

I need to move "Start_Date_Time" to the next business day with time value should be 8:00:00 AM (static value) ONLY if "Start_Date_Time" time value is greater than 5:00:00 PM otherwise leave "Start_Date_Time" as it is!

 

e.g. if "Start_date_time" = 20/09/2022 5:33:20 PM then it should move it to the next business day with a static hour value - which resulted in 21/09/2022 8:00:00 AM 

 

if  "Start_date_time" = 16/09/2022 5:33:20 PM then it should move it to the next business day with a static hour value - which resulted in 19/09/2022 8:00:00 AM

 

if  "Start_date_time" = 21/09/2022 3:10:20 PM then it should stays as it is!

 

I hope this will make it clear what I am looking for.

 

Please see if you can help.

 

Thank you,

Brijesh 

 

 

 

 

jbwtp
Solution Sage
Solution Sage

Hi Brijesh,

 

I think this is exactly what the code does:

jbwtp_1-1663796196114.png

 

There was a little ambiguity around what to do with  21/09/2022 3:10:20 AM (!).

I resolved it to applying same rule as (1) in your post and scroll to 8AM of the same day (kind of consistently apply rules for all "after-hours"). This is row #4 above.

 

Do you mind to test the code on your datetimes and send me a screenshot if it does anything wrong, so I could see the exception/where it fails.

 

P.S. Just had another thought. Where it may fail is the exact 5PM (5:00:00 PM) as this is not obvious if you want to treat it as received during "office hours" or "after hours" (I choose the later approach assuming that no one will deal with the request received in the last second of the working day). 

 

Thanks,

John 

 

Kind regards,

John

Thanks, John,

 

With your code above, do I need to add it to a new custom column or is it a new table? I need to create a new custom column that gives me desire result.

Please let me know.

 

Thanks again,

Brijesh

jbwtp
Solution Sage
Solution Sage

Hi Brijesh,

 

Yes it creates a new column. You can just add a dummy one (just set it to add a column of nulls), than replace the each bit with the following code:

each
   let 
      timeOffset = [Column1] - #duration(0,8,0,0), // makes 8 AM to be a start of the day
      shiftAfterHoursToNextDay = if Time.Hour(timeOffset) > 8 then Date.StartOfDay(timeOffset) + #duration(1,0,0,0) else timeOffset,
      isWeekend = List.Max({0, Date.DayOfWeek(shiftAfterHoursToNextDay)-4}),
      res = shiftAfterHoursToNextDay + #duration(if isWeekend > 0 then 2 / isWeekend else 0, 8,0,0)
   in res

You will also need to change the [Column1] reference int the code to the name of your datetime column.'

 

Cheers,

John

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors