• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Business Calendar Period Changes.

vinoblm

New Member
Hello,
in our business, calendar month was 1-31 or 1-30 or 1-28 or 1-29 according to the calendar. now they decided to change from 3rd of the current month to 2nd of the next month and is going to be every month. now when we do daily comparison of the sales with previous months, lets' assume, on 5th of the current month i have sales for 3-5 but in the previous records, 1-5 data. how we can tackle this issue. please help me,
 

vletm

Excel Ninja
Do You have clear sample to send here of Your challenge?
With information: what was before? and what would be soon?
 

vinoblm

New Member
Dear,

thanks for the quick response, i tried to attach but the 1.3 MB can't be uploaded hence i have pasted the dropbox link. Thanks in advance

 

vletm

Excel Ninja
vinoblm
There were features which won't work with my version ...
... but I tried to offer something.
With information: what was before? and what would be soon?
... hmm? Did I get an answer? ... hmm?
Seems that someone has time to do some long formulas ...
I didn't start to read those ...
but if BEFORE 'the 1st day of Month was the 1st day' and soon ...
'the 1st day of Month will be 3rd day' then
one possible 'as they =someone decide ...) would be like below:
1) Make a new column for 'NEW DATE' - okay?
2) Add two (2) for original (real) date ... and You'll get 'dates as they decided'.
3) After those ... use 'NEW DATE's for Your ... formulas.
Screenshot 2019-04-16 at 11.31.59.png
... and next week, You could solve ... next changes same way.
 

vinoblm

New Member
Thank you Dear.
but my situation is, since 3rd is new starting date, as on 10th of current month gives us 8 days'( 3rd to 10th) sale equals old month' 8 days sale which is 1st to 8th. anyway, thank you for the help. management decided to compare every month from 3rd whether old or new data. that is , as on 16th apr-19 sales means 3 to 16 from the current month and 3 to 16 from the old month as well. cheers
 

vletm

Excel Ninja
vinoblm
Did You get my idea or not?
Could You try to find an answer which would be ONLY YES or NO? ( not both )
Your sentences has maybe both variations.
My idea was and is that You should manipulate older dates as those would LOOK LIKE as they suddenly wanted.
Before 1st day of month was the 1st date ... now or is it soon ... the REAL 1st date is NOT the 1st REAL date of month!
If You manipulate dates then You could use normal eg Month-function as normally WITH those manipulated dates.
... then 'any day' would use as eg 1st day of month.
 

vletm

Excel Ninja
vinoblm
If not solved yet,
I would try to continue AFTER
You've read and done as forum rules needs:
Let us know, what will You do?
 

Chihiro

Excel Ninja
This type of scenario is typically handled by setting up calendar lookup table.

Be it in Excel sheet or in data modeling space.
 
Top