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

Daniboy

New Member
Joined
Oct 17, 2016
Messages
5
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!

Date 2018SalesTotal SalesDate 2019SalesTotal Sales%
Saturday, September 1, 201815001500Monday, September 2, 201910001000-33.33%
Monday, September 3, 201820003500Tuesday, September 3, 20193500450028.57%
Tuesday, September 4, 201818005300Wednesday, September 4, 20192900740039.62%
Wednesday, September 5, 201837509050Thursday, September 5, 2019180092001.66%
Thursday, September 6, 2018150010550Friday, September 6, 201932001240017.54%
Friday, September 7, 2018120011750Saturday, September 7, 201912501365016.17%
Saturday, September 8, 2018110012850Monday, September 9, 201940001765037.35%
Monday, September 10, 2018315016000Tuesday, September 10, 201919001955022.19%

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,207
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 17, 2016
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,955
Messages
5,411,486
Members
403,374
Latest member
PMMHart

This Week's Hot Topics

Top