Forum Discussion
Help with formula for different length months in accounting worksheet
I'm having the days of the month autofill but I don't know how to have it stop for months that have fewer days e.x. 28,29,30,31. I need it to be able to divide the profit margin by the days in the month but only for how long that specific month is. If its possible I'd like to also be able to tell it if we are closed for a holiday and push that cost onto the other days of the month.
I'm new to formulas and trying to self teach but this is way above my head any advice or help would be appreciated.
It depends on how you're generating the 'dates'. If the 'dates' are simply whole numbers then it won't skip a date like 2/30, for example.
You could generate the dates with SEQUENCE. This formula would generate 60 dates beginning on 2/1/23 and skip non-existant dates like 2/31/23, for example.
=SEQUENCE(60,,DATE(2023,2,1))- Well i don't know how you have "the days of the month autofill"
I recommend have a Start Date and then you have Total Days in Month based on:
=DAY(EOMONTH(startdate, 0))
Then you can 'autofill' in C19 using =startdate + SEQUENCE(1,B17,0)
