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>
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top