Show Status Per week\month\Quarter

[jagjit_singh]

Hi All,

I'm preparing data to show the status of property if it is occupied, with repairs (untenantable) or ready to let (Tenantable). I plan to export the end result to Tableau extract so users can check the property status by week, end of the month or quarter.

I have two tables, one which holds how long the property was vacant and also dates how long it was for repairs and with the allocation team before it is let. The second is the property table which hold property start and end date.I have blended the data and seems like i got the required results but need it to be reviewed to ensure, I have done the workflow correctly and looking for feedback.

Once I join the vacant and property table I then join the datset to a calendar marcro to get the dates (weeks, end of month and quarter). Though the months and quarter dates are correct the week start date and week end date is not as per Australian timezone and wanted help on how to fix the calendar macro.

I have attached the sample workbook and calendar macro and appreciate your assistance.

Thanks

[WayneWooldridge]

Hello @jagjit_singh!

I'm the person who put that calendar macro together, so I hope I'll be able to help you!  It's designed so Sunday is the first day of the week (use 2000-01-02 for example).  Not sure how you would like to have the WeekNumber to appear, so maybe you could provide some examples?  If you would like to tinker with the macro a bit, the WeekNumber field is calculated in the second Formula tool annoted with 'Time'. 

[jagjit_singh]

Hi Wayne,

Could you please help with updating the calendar macro to show start week as Monday (working week) and End as Friday as most of our caculations start on Monday.

Regards,

Jag

[WayneWooldridge · Accepted solution]

Sure.  The attached macro has been revised so Monday is the first day of the week.  You made mention of the work week (didn't say anything about Saturday and Sunday), so while Saturday and Sunday are still included in the macro, you can easily filter them out.