Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

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

Report
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 -
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 -
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

Answer 1 / 2
Posts
2656
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 17, 2020
 440
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
Answer 2 / 2
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
 213
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.
Ask a question