TODAY

scwyj · Apr 16th, 2002, 12:25 PM

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 · Apr 16th, 2002, 12:30 PM

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 · Apr 16th, 2002, 12:42 PM

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 · Apr 16th, 2002, 12:50 PM

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

should do the trick

Mark W. · Apr 16th, 2002, 02:47 PM

Quote:

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 16th, 2002, 12:25 PM	#1
scwyj New Member Join Date: Feb 2002 Location: East Sussex Posts: 33	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

Apr 16th, 2002, 12:30 PM	#2
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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.

Apr 16th, 2002, 12:42 PM	#3
scwyj New Member Join Date: Feb 2002 Location: East Sussex Posts: 33	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

Apr 16th, 2002, 12:50 PM	#4
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	=(DAY(DATE(YEAR(NOW()),MONTH(NOW())+1,0)))/(DAY(NOW())-1) should do the trick