Previous Topic Next Topic

Formula to increment date by seven days.

Solved/Closed
vcoolio Posts 1364 Registration date Thursday July 24, 2014 Status Moderator Last seen February 10, 2023 - Sep 25, 2014 at 09:22 AM
vcoolio Posts 1364 Registration date Thursday July 24, 2014 Status Moderator Last seen February 10, 2023 - 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



2 replies

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Sep 25, 2014 at 11:25 AM
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
0
Answer 2 / 2
vcoolio Posts 1364 Registration date Thursday July 24, 2014 Status Moderator Last seen February 10, 2023 252
Sep 26, 2014 at 03:07 AM
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.
0