Pace minus a day?

[Erkjerk]

The formula I have in place is =E1/DAY(TODAY())*31 for January. This works great as long as we don't close for a day in the month. Of course we were closed January 1st. How can i make my current formula not calculate jan 1st in the pace? Thanks.

 

[footoo]

I would suggest :
=E1/DAY(TODAY()-1)*30

Or if you want to put the days closed in F1 :
=E1/DAY(TODAY()-F1)*(31-F1)

Or if you want the formula to automatically use the number of days in the current month :
=E1/DAY(TODAY()-F1)*(DAY(EOMONTH(TODAY(),0)-F1))

 

[Erkjerk]

What does the -1 signify?

 

[footoo]

One day closed.

 

[Erkjerk]

For the f1 formula, woukd i just put 1/1/2018, and it would know to not calculate that day?

 

[footoo]

For the f1 formula, woukd i just put 1/1/2018, and it would know to not calculate that day?

No. Enter 1 in F1

 

[Erkjerk]

You wouldnt do =E1/DAY(TODAY()-1)*31?

 

[footoo]

No. Because there are only 30 non-closed days in the month.
I presume you want to return the projected total for the month based on the daily actual average so far.
For example, assuming 10 per day from 2 Jan to 10 Jan, the projected total for the whole month should be 300, not 310.

 

[Erkjerk]

Makes sense. Should i not be pulling from today () but instead day before?

 

[footoo]

Yes, if your figures are only up to the day before (the day before would be Day(Today()-1) instead of Day(Today())