Count Week of Year from Specific Hour in Day

[vojtechsima]

Hello,

I'd like to determine the Start of the Week in Year based on a Specific Hour in Day.

I want to start the Week at 08:00 AM on Friday. Meaning Friday 07:59 would be Week 13 and Friday 08:00 Week 14 already.

if [Priority] = "Critical" & [Issue Type] = "Incident" then Date.WeekOfYear([Resolved], Day.Friday+#duration(0,8,0,0)) else Date.WeekOfYear([Created], Day.Friday+#duration(0,8,0,0))

So far I tried to work with adding duration but I can't make it work. Could you please advise me on how to properly solve it?

Thank you

[Anonymous · Accepted solution]

Adding the duration to Day.Friday etc... will not work because Day.Friday or Day.Monday etc... are not DateTime values.

What you could do instead is check if your fields [Resolved] and [Created] have timezone values attached to them, if not add them, and then use the DateTimeZone.SwitchZone function to switch the timezone back by 08 hours. 08:00 AM onwards will be shifted back by 08 hours and the system will think it is midnight and then use this temporary timezone switched DateTime value to calculate the Week Number. Any time earlier than 08:00 AM will be pushed to the previous date because of the shift in timezone so that will get handled accordingly.

Another alternative approach would be to convert the DateTime values to decimal numbers, manipulate them, and turn those manipulated numbers back to DateTime and then calculate the Week Number. When I say, manipulate, I mean that internally a DateTime value is stored as a decimal number where the integer part is the days and the fractional/decimal part is the time value. One hour = 1/24, One Minute = 1/(24*60) and One Second = 1/(24*60*60) and so on like 1 Milli Second = 1/(24*60*60*1000) etc... Once you have the DateTime values converted to decimal numbers you can just reduce 08 hours (1/3) from that number and convert it back to a DateTime.

There are many possibilities.

[Anonymous · Accepted solution]

Adding the duration to Day.Friday etc... will not work because Day.Friday or Day.Monday etc... are not DateTime values.

What you could do instead is check if your fields [Resolved] and [Created] have timezone values attached to them, if not add them, and then use the DateTimeZone.SwitchZone function to switch the timezone back by 08 hours. 08:00 AM onwards will be shifted back by 08 hours and the system will think it is midnight and then use this temporary timezone switched DateTime value to calculate the Week Number. Any time earlier than 08:00 AM will be pushed to the previous date because of the shift in timezone so that will get handled accordingly.

Another alternative approach would be to convert the DateTime values to decimal numbers, manipulate them, and turn those manipulated numbers back to DateTime and then calculate the Week Number. When I say, manipulate, I mean that internally a DateTime value is stored as a decimal number where the integer part is the days and the fractional/decimal part is the time value. One hour = 1/24, One Minute = 1/(24*60) and One Second = 1/(24*60*60) and so on like 1 Milli Second = 1/(24*60*60*1000) etc... Once you have the DateTime values converted to decimal numbers you can just reduce 08 hours (1/3) from that number and convert it back to a DateTime.

There are many possibilities.

[vojtechsima · Accepted solution]

Thank you, this is how I actually ended up doing it, I subtract 8 hours from [Resolved] and [Created].

Here is the final code:

if [Priority] = "Critical" & [Issue Type] = "Incident" then Date.WeekOfYear([Resolved]-#duration(0,8,0,0), Day.Friday) else Date.WeekOfYear([Created]-#duration(0,8,0,0), Day.Friday)

[Anonymous]

Simple and Elegant 🙂