Hello All! I am in need of help to calculate two sales run rates using
formulas.

The formula I currently use for a monthly run rate is:
=SUM(MTD Sales/Today's day # in month * Total # Days in month)
I manually add up number of weekdays in month (minus holidays) & today's day
# in month.

Through browsing the board, I am using the following formula to figure
number of weekdays in date range minus holidays (K1:K40)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J2&":"&J3)),2)<6))-
COUNT(K1:K40)

(1) How do I calculate monthly run rate based on today's date?
(2) How do I calculate quarterly run rate based on today's date?

TIA for any suggestions :-)

LoriM, not sure if I fully understand your problem. But here are the two
functions that may help: today() gets you today's date; and eomonth (such as
EOMONTH(TODAY(),0)) will push to the end of month. EOMONTH can save your
manual work to find out the number of days in current month. By changing 0
to 2, for example, you can push today's date 2 months ahead and find the
month end date.

With the combination of the weekday function, I hope you can what you need.
Or simply provide a simple example with cell number and value and what you
want to get out of it...



"LoriM" wrote:

Hello All! I am in need of help to calculate two sales run rates using
formulas.

The formula I currently use for a monthly run rate is:
=SUM(MTD Sales/Today's day # in month * Total # Days in month)
I manually add up number of weekdays in month (minus holidays) & today's day
# in month.

Through browsing the board, I am using the following formula to figure
number of weekdays in date range minus holidays (K1:K40)

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(J2&":"&J3)),2)<6))-
COUNT(K1:K40)

(1) How do I calculate monthly run rate based on today's date?
(2) How do I calculate quarterly run rate based on today's date?

TIA for any suggestions :-)

Hi there! I'd be happy to help you with your sales run rate calculations.

To calculate the monthly run rate based on today's date, you can use the following formula:

1. =SUM(YTD Sales/(TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1)*DAY(EOMONT H(TODAY(),0)))

Let me break down this formula for you:

- YTD Sales: This is the total sales for the year up to today's date. You'll need to replace this with the cell reference for your actual YTD sales figure.
- TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1: This calculates the number of days from the first day of the current month to today's date. For example, if today is June 15th, this part of the formula would calculate 15.
- DAY(EOMONTH(TODAY(),0)): This calculates the total number of days in the current month. For example, if today is June 15th, this part of the formula would calculate 30 (since June has 30 days).

Putting it all together, this formula will give you the monthly run rate based on today's date.

To calculate the quarterly run rate based on today's date, you can use a similar formula:

1. =SUM(YTD Sales/(TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY())-MOD(MONTH(TODAY())-1,3),1)+1)*DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(TO DAY())+2,1),0)))

Again, let me break down this formula for you:

- YTD Sales: This is the total sales for the year up to today's date. You'll need to replace this with the cell reference for your actual YTD sales figure.
- TODAY()-DATE(YEAR(TODAY()),MONTH(TODAY())-MOD(MONTH(TODAY())-1,3),1)+1: This calculates the number of days from the first day of the current quarter to today's date. For example, if today is June 15th, this part of the formula would calculate 46 (since the first day of the current quarter was April 1st).
- DAY(EOMONTH(DATE(YEAR(TODAY()),MONTH(TODAY())+2,1) ,0)): This calculates the total number of days in the current quarter. For example, if today is June 15th, this part of the formula would calculate 91 (since the current quarter ends on June 30th).

Putting it all together, this formula will give you the quarterly run rate based on today's date.