Please help

[MikeyMike1828]

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

 

[rs2k]

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]

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