Sales from days of this month vs same quantity of days same month last year.

[Daniboy]

Hello, first of all thanks for your help.

What I want to do is to compare the cumulative sales from working days since first of the month to current date vs the sales of the same quantity of working days same month last year.

Lets say today is September 4, 2019 and we work from Monday to Saturday.
As September 2019 started Sunday my first working day was Monday 2nd, second working day was Tuesday 3rd and third working day was Wednesday 4th.
Last year September 1st was Saturday as we work on Saturdays my first working day was that Saturday, second working day was Monday 3rd, and third working day was Tuesday 4th.

Is there any way to compare this periods of time?

This is just an example I did manually, I'm looking for a formula to do it automatically for the whole year.

Thanks!

[TABLE="width: 755"]
<tbody>[TR]
[TD="class: xl65, width: 221, align: center"]Date 2018[/TD]
[TD="class: xl66, width: 44, align: center"]Sales[/TD]
[TD="class: xl66, width: 81, align: center"]Total Sales[/TD]
[TD="class: xl66, width: 221, align: center"]Date 2019[/TD]
[TD="class: xl66, width: 44, align: center"]Sales[/TD]
[TD="class: xl66, width: 81, align: center"]Total Sales[/TD]
[TD="class: xl67, width: 63, align: center"]%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Saturday, September 1, 2018[/TD]
[TD="align: right"]1500[/TD]
[TD="class: xl68, align: right"]1500[/TD]
[TD="class: xl63, align: right"]Monday, September 2, 2019[/TD]
[TD="align: right"]1000[/TD]
[TD="class: xl68, align: right"]1000[/TD]
[TD="class: xl69, align: right"]-33.33%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Monday, September 3, 2018[/TD]
[TD="align: right"]2000[/TD]
[TD="class: xl68, align: right"]3500[/TD]
[TD="class: xl63, align: right"]Tuesday, September 3, 2019[/TD]
[TD="align: right"]3500[/TD]
[TD="class: xl68, align: right"]4500[/TD]
[TD="class: xl69, align: right"]28.57%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Tuesday, September 4, 2018[/TD]
[TD="align: right"]1800[/TD]
[TD="class: xl68, align: right"]5300[/TD]
[TD="class: xl63, align: right"]Wednesday, September 4, 2019[/TD]
[TD="align: right"]2900[/TD]
[TD="class: xl68, align: right"]7400[/TD]
[TD="class: xl69, align: right"]39.62%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Wednesday, September 5, 2018[/TD]
[TD="align: right"]3750[/TD]
[TD="class: xl68, align: right"]9050[/TD]
[TD="class: xl63, align: right"]Thursday, September 5, 2019[/TD]
[TD="align: right"]1800[/TD]
[TD="class: xl68, align: right"]9200[/TD]
[TD="class: xl69, align: right"]1.66%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Thursday, September 6, 2018[/TD]
[TD="align: right"]1500[/TD]
[TD="class: xl68, align: right"]10550[/TD]
[TD="class: xl63, align: right"]Friday, September 6, 2019[/TD]
[TD="align: right"]3200[/TD]
[TD="class: xl68, align: right"]12400[/TD]
[TD="class: xl69, align: right"]17.54%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Friday, September 7, 2018[/TD]
[TD="align: right"]1200[/TD]
[TD="class: xl68, align: right"]11750[/TD]
[TD="class: xl63, align: right"]Saturday, September 7, 2019[/TD]
[TD="align: right"]1250[/TD]
[TD="class: xl68, align: right"]13650[/TD]
[TD="class: xl69, align: right"]16.17%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Saturday, September 8, 2018[/TD]
[TD="align: right"]1100[/TD]
[TD="class: xl68, align: right"]12850[/TD]
[TD="class: xl63, align: right"]Monday, September 9, 2019[/TD]
[TD="align: right"]4000[/TD]
[TD="class: xl68, align: right"]17650[/TD]
[TD="class: xl69, align: right"]37.35%[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Monday, September 10, 2018[/TD]
[TD="align: right"]3150[/TD]
[TD="class: xl68, align: right"]16000[/TD]
[TD="class: xl63, align: right"]Tuesday, September 10, 2019[/TD]
[TD="align: right"]1900[/TD]
[TD="class: xl68, align: right"]19550[/TD]
[TD="class: xl69, align: right"]22.19%[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi Daniboy,
it took me a bit of fiddling, but this formula gives the number of workdays in that month for a given day (assuming only sundays are non-workdays).
=DAY(H2)+((DAY(H2)-WEEKDAY(H2,17)+WEEKDAY(DATE(YEAR(H2),MONTH(H2),1),3))/-7)
But other than that: if you want to compare 2 years: I assume you have one cell with the value 01-10-2019 (first day of current month), assuming that in A1, you could do:
=DATE(YEAR(A1),MONTH(A1),1)+IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),3)=6,1,0)
And for the first day of last year, same month:
=DATE(YEAR(A1)-1,MONTH(A1),1)+IF(WEEKDAY(DATE(YEAR(A1)-1,MONTH(A1),1),3)=6,1,0)
After that it's basically +1 for the next day, again correcting for the Sunday.
Hope that helps a bit,
Koen

 

[Daniboy]

Hi Daniboy,
it took me a bit of fiddling, but this formula gives the number of workdays in that month for a given day (assuming only sundays are non-workdays).
=DAY(H2)+((DAY(H2)-WEEKDAY(H2,17)+WEEKDAY(DATE(YEAR(H2),MONTH(H2),1),3))/-7)
But other than that: if you want to compare 2 years: I assume you have one cell with the value 01-10-2019 (first day of current month), assuming that in A1, you could do:
=DATE(YEAR(A1),MONTH(A1),1)+IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1),3)=6,1,0)
And for the first day of last year, same month:
=DATE(YEAR(A1)-1,MONTH(A1),1)+IF(WEEKDAY(DATE(YEAR(A1)-1,MONTH(A1),1),3)=6,1,0)
After that it's basically +1 for the next day, again correcting for the Sunday.
Hope that helps a bit,
Koen

Thank you for that, will do some testing and get back to you!
Daniel