Convert data to show each day of week and week summary

Hi All,

I have a list of time entires, for march 2024 , date and hours. I am trying to convert this into a summary by week that shows the total hours per week and total per day , where each day is a column MTWTFSS.

I have got part of the way there with the attached transform, but not sure if my approach makes sense, or will hit performance issues , and also how to do a summary by week? I looked at Summary and Total transforms but didnt seem to be a way to sum all the hours for each day for each week based on my data.

any ideas?

DayOfWeek.transform (8.6 KB)


I would do it with WeekOfYear, DayOfYear and a Pivot. No need for Ifs.


DayOfWeek2.transform (5.2 KB)

This approach should scale to millions of rows, with no problem.

thanks for this Andy, i also should have said that it still needs the week start and week end dates, is it still possible to add that detail to this using this approach?

DayOfWeek2 extended.transform (10.6 KB)

Start and end dates of the week are added, if they exist in input data.

Here is another approach. Might get more complicated if you are spanning multiple years.


DayOfWeek3.transform (11.0 KB)

Here is one.

I noted that in your data 31/03/2024 had total hours 39, I take it, that is total for the month? in my solution I removed this number and put arbitrary hours, so that the Sum of hours are correct. So if you try out my solution make sure you don’t have any total hours for the month or week in any of your dates.

Transform file.
DayOfWeekAndSummary.transform (10.6 KB)

thanks for this Olaf, i will try and work this out!

thanks @Anonymous , i think this provides the solution, although not sure I understand all of it!

i am thinking to remove the row and column totals/sum parts, how much could this simplify this transform? so i then just have the below columns.

week start, week end, mon,tue,wed,thurs,fri,sat,sun

Here it is, as per your request.

Transform file.
DayOfWeekAndSummary2.transform (7.8 KB)

1 Like

many thanks for this!

HI @simonj , I am big proponent of having a Date Master file which contains all calculations in advance.

See small snippet of the file for Year 2024

Date for forum.csv (69.6 KB)

using that I can do a simple lookup for what you want

DayOfWeek.transform (5.2 KB)


a good option, thanks!

1 Like