I have some dates written down in some cells and I need to change all the days to 4 days later but I dont want to calculate and change each day seperately. is there a formula for it? I need it changed in the original cell. Ex. I have Jan 23, 2012 in b7 but I want it to be Jan 27, 2012 so how do I do that?

✔ Best Answer

You can't have a value in a cell and a formula that works on that value in the same cell. Think about it...how would that work? Once you added 4 to the value your formula would want to add 4 again and then add 4 again and then add 4 again, etc.

I can think of 2 options to get it done in the same cells as the dates:

Option 1:

1 - Enter 4 in an empty cell

2 - Right click...Copy

3 - Select your dates

4 - Right-click...Edit...Paste Special...Add

5 - You might have to reformat the cells as Date if they turn into date-serial numbers.Option 2:

Change the Range in this macro to match your Range and run it.

Sub Add4() For Each myDate In Range("B7:B22") myDate.Value = myDate.Value + 4 Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Providing to a date is formatted as a date and not as a string just add 4. It is a simple as that. Stuart

But how can I do that in the same cell as the original date is written? what is the formula?

You don't You create another row or column. in that row or column you put the formula b7 + 4 and copy it all the way down. The you delete the original

Stuart

I knew how to do that. I thought maybe there was a way to do it in the same cell

You can't have a value in a cell and a formula that works on that value in the same cell. Think about it...how would that work? Once you added 4 to the value your formula would want to add 4 again and then add 4 again and then add 4 again, etc.

I can think of 2 options to get it done in the same cells as the dates:

Option 1:

1 - Enter 4 in an empty cell

2 - Right click...Copy

3 - Select your dates

4 - Right-click...Edit...Paste Special...Add

5 - You might have to reformat the cells as Date if they turn into date-serial numbers.Option 2:

Change the Range in this macro to match your Range and run it.

Sub Add4() For Each myDate In Range("B7:B22") myDate.Value = myDate.Value + 4 Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll