Hello, there. I've been digging around the web for the past few weeks trying to find an easy way to split a table of invoices by the weeks that they span. I'm rapidly coming to the conclusion that Excel is mainly set up for allocating by month rather than week, as haven't found much that can help.
I've set up the attached spreadsheet. Column B shows the start of each billing period and Column C shows the end of each billing period (both dates inclusive, e.g. in row 7, 28 May should be included). Column D shows the cost over that period.
What I want to do is then split each cost on a weekly basis. I've added a couple of columns that I assume are useful. I guess the formula would go something like:
Work out how many days the invoice covers (column F)
Therefore work out the cost per day (column G)
What I then don't know how to do is to compare the dates in column B and C with the dates in each week in rows 2 and 3.
The first invoice is outside the date range (period end date is before the Wk 1 start date) and would thus be zero.
The second invoice includes 7 days from Wk 1, 7 days from Wk 2, 7 days from Wk 3 and 4 days from Wk 4. Multiply the number of days identified in each week by the cost per day.
Everything after Wk 4 for the second invoice is outside the date range (period end date is before the Wk 5 start date) and would thus be zero.
I'd like to be able to change the start date (cell B1) and the period start dates and period end dates (columns B and C) and for the formula to still work. At the moment, none of the billing periods overlap, but there is no reason why they couldn't. So for example, invoice 4 covers the period 24 July - 20 August and invoice 5 covers the period 21 August - 17 September. There's no reason invoice 5 couldn't start on 10 August though, i.e. there would be some dates that are covered by both invoices. They're in separate rows, but just thought I would mention.
Any help very gratefully received!
Thanks,
Ben
I've set up the attached spreadsheet. Column B shows the start of each billing period and Column C shows the end of each billing period (both dates inclusive, e.g. in row 7, 28 May should be included). Column D shows the cost over that period.
What I want to do is then split each cost on a weekly basis. I've added a couple of columns that I assume are useful. I guess the formula would go something like:
Work out how many days the invoice covers (column F)
Therefore work out the cost per day (column G)
What I then don't know how to do is to compare the dates in column B and C with the dates in each week in rows 2 and 3.
The first invoice is outside the date range (period end date is before the Wk 1 start date) and would thus be zero.
The second invoice includes 7 days from Wk 1, 7 days from Wk 2, 7 days from Wk 3 and 4 days from Wk 4. Multiply the number of days identified in each week by the cost per day.
Everything after Wk 4 for the second invoice is outside the date range (period end date is before the Wk 5 start date) and would thus be zero.
I'd like to be able to change the start date (cell B1) and the period start dates and period end dates (columns B and C) and for the formula to still work. At the moment, none of the billing periods overlap, but there is no reason why they couldn't. So for example, invoice 4 covers the period 24 July - 20 August and invoice 5 covers the period 21 August - 17 September. There's no reason invoice 5 couldn't start on 10 August though, i.e. there would be some dates that are covered by both invoices. They're in separate rows, but just thought I would mention.
Any help very gratefully received!
Thanks,
Ben