#### MikeyMike1828

##### New Member
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### rs2k

##### Well-known Member
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
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

Replies
2
Views
77
Replies
5
Views
126
Replies
3
Views
73
Replies
1
Views
394
Replies
5
Views
113

1,181,251
Messages
5,928,923
Members
436,635
Latest member
mercessb

### 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.

### Which adblocker are you using?

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

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