a formula that Distributes number of days between 2 dates within weekly basis

Occasional Visitor

Hi ,


I need to distribute the number of days between a start and end date within the year but instead of months, I need it to show me the number of days within the weeks. 

I am trying to add  a maternity /paternity forecast to salary budgets


1 Reply

@chatlotte_ss , I assume you have a column of data in Excel that is date/time stamp. And, if so, I would start by using the 'Week' Excel date time function to create a new column of data: in your case a Weekly Period field that would assign a value from 1 to 52 to each record based on the date/time stamp. Then, from here, it would be easy to count the number of records within each weekly period from start to end. I have attached a sample output using data and a template I have for automating this. The first column in this template - shaded in grey - would be like your date time stamp data. I created all the other columns from this...and you can see the Weekly Period column I refer to. In my sample, the Weekly Period values are all 1 since all the data falls within week 1. However, if you have an entire year's worth of data, your records would have values anywhere between 1 and 52 depending on which week the date time stamp data falls. The great thing about this approach, is that by creating your new column, now you can isolate the level of details (eg. in your case, weeks) at which you would now like to forecast using the various Excel forecasting functions and charts.