TODAY

[scwyj]

Hello,

I am trying to use the TODAY function to calculate monthly sales based on sales to date, i.e. if today is the 16th, I want to take total sales month-to-date, and extend it to 30/16ths of the sales total.

Any ideas?

Nanette

 

[IML]

you could use
=(DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)))/DAY(NOW())

as your multiplier. now() used instead of today() to reduce carpel tunnel.

 

[scwyj]

Thank you for your prompt reply. The formula worked well. Now that it is working, I am told that our MTD sales are to YESTERDAY, not today.

How can I modify this formula to calculate yesterday's factor?

NW

 

[IML]

=(DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)))/(DAY(NOW())-1)

should do the trick

 

[Mark W.]

On 2002-04-16 11:42, scwyj wrote:
Thank you for your prompt reply. The formula worked well. Now that it is working, I am told that our MTD sales are to YESTERDAY, not today.

How can I modify this formula to calculate yesterday's factor?

NW

This formula calculates your Projected Monthly Sales based on MTD Sales (in cell A1) thru yesterday...

=TREND({0,1}*A1,{0,1}*DAY(TODAY()-1),DAY(EOMONTH(TODAY(),0)))

Note: This formula uses the EOMONTH function which is supplied by the Analysis ToolPak. See the Excel Help topic for "Install and use the Analysis ToolPak" if you need to install this add-in.
This message was edited by Mark W. on 2002-04-16 13:52