How to add 30 days(in column B) to certain date of column A automatically

[Sandy_Patel]

Hello

Find attached sheet which I used in my previous job. When I enter date in column A then adjacent column B gets updated automatically with 30 days adding to date of column A . For example, if I type date as 18/06/2014 to cell A15 followed by pressing ENTER then cell B15 automatically add 30 days to date of cell A15.

I don’t know how this work. In my previous job, whatever I entered date of invoice , due date occurred after 30 days to that date. So this feature is so handy.

After typing date to column A, column B gets formula to add 30 days to relevant column A. I don’t know how this work. Please tell me, how can I do this in newly created sheet by me ?

Thanks

 

[Hui]

Sandip

If you go to cell B3 you will see it contains =A3+30
This says add 30 to the value of cell A3
Days in Excel are stored as Integers
Today 18 June 2014 is 41808
Tomorrow 19 June will be 41809

So you can simply add whole numbers to a date to get a new date

In the above formula in Cell B3: =A3+30
The A3 refers to the cell at position A3 which contains 11/7/2012
If you select A3 and click on the , button it will display the date number of 41101
In B3 this equates now to =41101+30
=41131
which is the Date value for the answer of 10/8/2012

To understand more about dates, have a read of:
http://chandoo.org/wp/2013/10/17/excel-date-time-tips/

 

[Sandy_Patel]

Hi Hui

I got ur point. Nice details of date, how it works given by you.

But logic of my question is different. Assume I have created new excel sheet. Now I type certain date in cell A1 and put formula to cell B1 as =A1+30. After this next time, when I type any date to cell A2 and press enter then I want cell B2 automatically gets updated by formula as =A2+30 without typing any formula to cell B2. Means cell B2 should follow formula of cell B1. And so on when I type any date to column A then relevant cell of column B gets updated by adding 30 days to date of column A.

One way to do this is that put date to cell A1 and put above formula to B1. Then again put any date in cell A2 and put formula to cell B2 and continue this practice till cell A4 and B4. Then when I put any date to cell A5 followed by pressing enter, cell B5 automatically gets updated by adding 30 days to cell A5.

Other way is that I can set formula to cell B1 as =IF(A1="","",A1+30) to add 30 days to date of cell A1 and drag this formula into column B.

But my question is, is there anyway that I put date in cell A1 then set formula to cell B1 as =A1+30 and after this when I put any date to cell A2 then cell B2 automatically gets updated with adding 30 days to cell A2 without typing any formula to cell B2 ? Means cell B2 follows formula of B1 without typing any formula to cell B2. Is there other creative way to update date automatically ? Hope, I have cleared my question.


Thanks

 

[p45cal]

see http://office.microsoft.com/en-gb/e...-formulas-to-additional-rows-HP005229125.aspx

 

[Hui]

If your dates are in A2:A100
in B2 simply use: =A2+30
Copy B2 down to B100
Then select B2:B100
Apply a custom Number format like: [<100]"";dd mmm yyyy
Adjust date format to suit your needs
This will only display dates greater than 9 April 1900

But be careful
Any cells referring to Column B will see the hidden dates as values in 1900

 

[Sandy_Patel]

see http://office.microsoft.com/en-gb/e...-formulas-to-additional-rows-HP005229125.aspx

Thanks


Cheers

 

[Sandy_Patel]

Hi Hui
I got reply to my answer.
I opened website link sent by p45scal and came to know, in order to repeat format of preceding rows, there must format in 3 preceding rows .


Thanks a lot again