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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### 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
126
Replies
6
Views
248
Replies
2
Views
143
Replies
1
Views
179
Replies
6
Views
192

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,480
Messages
5,770,341
Members
425,612
Latest member
martinijr

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