Formula to increment date by seven days. [Solved/Closed]

vcoolio 1173 Posts Thursday July 24, 2014Registration dateContributorStatus July 15, 2018 Last seen - Sep 25, 2014 at 09:22 AM - Latest reply: vcoolio 1173 Posts Thursday July 24, 2014Registration dateContributorStatus July 15, 2018 Last seen
- Sep 26, 2014 at 03:07 AM
Hello Everyone,

In a work sheet, I have an invoice date (say column A) and a due date (say column H) with the due date being seven days after the invoice date. Because I have become somewhat lazy, I have used the following formula in column H to automatically increment the "invoice date" by seven days to become the "due date":-

=A1+7 (placed in H1 with the subsequent cells following suit, i.e. A2+7, A3+7 etc. placed in the corresponding cell in column H).

The formula works OK but the date 7/01/1900 appears & stays in each cell in column H until such time as I place a date in column A. The correct incremented date then appears.

Does anyone know how to get rid of the annoying 7/01/1900?

Thanks and regards,
vcoolio



See more 

2 replies

TrowaD 2396 Posts Sunday September 12, 2010Registration dateModeratorStatus July 19, 2018 Last seen - Sep 25, 2014 at 11:25 AM
0
Thank you
Hi Vcoolio,

Sure, no problem!

=IF(A1="","",A1+7)
If A1 is empty, show nothing.
If A1 is not empty, then add 7 to it.

Best regards,
Trowa
vcoolio 1173 Posts Thursday July 24, 2014Registration dateContributorStatus July 15, 2018 Last seen - Sep 26, 2014 at 03:07 AM
0
Thank you
Hello again Trowa,

Thanks for that. Much appreciated.
This is actually another of the wife's projects so you've saved me from the dog house again!

After posting the thread, I played around with formulas (formulae?) for about an hour and found that the following ISBLANK function works well also:-

=IF(ISBLANK(A1),"",A1+7)

but I like your formula better so I've inserted it to replace the ISBLANK one.

I can't believe how lazy the wife and I have become (can't even type in a date a few columns away)!!

BTW, thanks for explaining the formula also.

Anyway, grazie molto.
vcoolio.