Solved Add 60 days to date IF there is a date...

Excel Excel 2007
June 15, 2011 at 07:06:18
Specs: Windows 7
I know how to add 60 days to the date (=REFCELLWITHDATE+60). My question, however, is what is the formula to add that 60 days ONLY IF there is a date. I have dates in F6, F7, F8, and on.... I want to add 60 days to those cells that have a date entered. Make sense?

Thanks in advance!


See More: Add 60 days to date IF there is a date...

Report •


#1
June 15, 2011 at 07:22:39
✔ Best Answer
Make sense?

Not really,
you can't add 60 days to a cell with date data already in the cell.
IE: Cell A1 contains the date: 06/15/2011

You can add 60 days to the date and have the result in another cell.
IE: Cell A1 contains the date: 06/15/2011 and Cell B1 =A1+60

To increment the date in cell A1 you either have to manually update it, or get a piece of VBA code to do it. Sorry, my VBA code skills are nill.

If you want the second scenario then try this

In cell B1 enter: (DerbyDad03 created this one)

=IF(NOT(ISERROR(DATEVALUE(TEXT(A1,"mm/dd/yyyy")))),A1+60,"")

This will insure that cell A1 contains a Date, not just any data.

MIKE

http://www.skeptic.com/


Report •

#2
June 15, 2011 at 07:40:04
re: "To increment the date in cell A1 you either have to manually update it..."

Which could be done just by re-entering the date or by using PasteSpecial..Add if you have to change a range of cells.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
June 15, 2011 at 08:26:34
AWESOME!
=IF(NOT(ISERROR(DATEVALUE(TEXT(F6,"mm/dd/yyyy")))),F6+60,"")

I should've been more clear... My date is in F6, F7, Etc... and the +60 days will be in H6, H7, etc... provided that there IS a date in the F column.
The formula from DerbyDad03 that I took from your reply worked perfectly!
Thank you to both of you! I think I'll be a frequent flyer in this forum.... :)
Have a great day!


Report •

Related Solutions

#4
June 15, 2011 at 10:22:49
What if I want it to be "+60 BUSINESS days"????

Report •

#5
June 15, 2011 at 10:43:28
Try something like:

=IF(NOT(ISERROR(DATEVALUE(TEXT(A1,"mm/dd/yyyy")))),WORKDAY(A1,60),"")

Lookup the =WORKDAY() function in your help files,
because you can also include holidays.

EDIT ADDED:

Requires the Analysis Toolpak Add-In


MIKE

http://www.skeptic.com/


Report •

#6
June 16, 2011 at 07:13:41
Fantastic. Thanks a bunch, Mike! :)

Report •

Ask Question