Please help

MikeyMike1828

New Member
Joined
Dec 17, 2004
Messages
9
I need to keep track of monthly sales but the months ends in the middle of the month example Let's say a sales month ends 01/21/2005. How can I have a cell automatically change to 02/01/2005? I tried using the function date and the today function. Once the month changes in the today function the date function would change. Example if today's date was 01/31/2005 the cell that has the date function would be 1. Then when it turns to February the the cell with the function date would be 2. There has to be a easier way to do this. If that doesn't make any sense I will try to explain a better.

Thanks
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
You could try adding 11 days to the today formula and useing that for your reference

example A1 =TODAY()
B1 = A1+11 which gives you 02/01/05

use B1 to reference everything you need!
Just a thought

Colin.
 

yankee428

Active Member
Joined
Apr 12, 2004
Messages
348
First off you should slap the sales people for being stupid.

After that I would recommend using an if statement to bump anything over 1/21/05 to read as 2/1/05. That should look like this:

=IF(AND(date>38373,date<38384),38384,date)

where date is the range with the date you would like to evaluate.

The problem is that you would then need to change the serial date number in this formula every month and would need some business logic to dictate, ie if Jan ends on 2/21, then when does feb end? 2/21 or 2/18?

If it was 2/21 then the formula would look like this:
=IF(AND(date>38404,date<38412),38412,date)

Maybe someone else can come up with a better solution. If not, please refer to my solution #1
 

Forum statistics

Threads
1,148,050
Messages
5,744,513
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top