Formula to increment date by seven days.Solved/Closed

Question

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



Configuration: Windows 7 / Mozilla 11.0

TrowaD · Answer

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 · Answer

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.

Formula to increment date by seven days.

2 replies

Subscribe To Our Newsletter!