Insert a future date based on a number in another cell

**LongShanks** · 05-10-2010, 07:42 AM

Hi

i'm trying to use an IF statement to insert a working day date later than today's date based on a number in another cell. For example if C2 has a number 5 in it then a date should automatically be entered, this being 5 working days from now.

here is what i have at the moment

=IF(C1:C5000="2",WORKDAY(TODAY(),2,""))*IF(C1:C5000="1",WORKDAY(TODAY(),1,""))*IF(C1:C5000="5",WORKDAY(TODAY(),,""))*IF(C1:C5000="3",WORKDAY(TODAY(),3,""))

but is does not work as i get this 00/01/1900 in each cell.

I'm new to this and i'm learning as i go along but this has me stumped!!

**daddylonglegs** · 05-10-2010, 08:03 AM

Your formula probably doesn't work because you are using quotes around numbers like "3". That will only work if your column is text-formatted. If they are actual numbers you shouldn't use the quotes......however I'm not sure why you can't use this formula in row 1 copied down.

=WORKDAY(TODAY(),C1)

if you want to exclude cells which don't have numbers you could amend to

=IF(ISNUMBER(C1),WORKDAY(TODAY(),C1),"")

or for specific number only

=IF(OR(C1={1,2,3,5}),WORKDAY(TODAY(),C1),"")

**LongShanks** · 05-10-2010, 08:28 AM

thanks daddylonglegs,

=WORKDAY(TODAY(),C1) works fine but i have to change C1 to C2 and so on which is quite cumbersome as there are a few hundered entries, is there a way of using a range such as C1:C5000? I tried amending your code but it gave me an error.

**daddylonglegs** · 05-10-2010, 08:44 AM

If you use the formula

=WORKDAY(TODAY(),C1)

in D1 then you can "fill" that formula down the column and C1 will automatically change to C2, C3 etc. There are a number of ways to fill down:

If you select cell D1 and then put the cursor on the bottom right corner of the cell you should see a black "+" - this is the "fill handle". If you now left-click the mouse and hold down you can just "drag" the formula down the column.

If you have continuous data in column C you can just double click the fill-handle and it will automatically copy your formula as far down as you have data

**LongShanks** · 05-10-2010, 09:28 AM

thanks again daddylonglegs,

this works great however, i want my spreadsheet to show blank cells until someone enters some data after which the information in column D will populate but not before. At the moment the cells in column D have (hash)VALUE! all the way down the column although this does change after a number is entered into column C.

**daddylonglegs** · 05-10-2010, 09:34 AM

Try using the second formula I suggested, i.e.

=IF(ISNUMBER(C1),WORKDAY(TODAY(),C1),"")

That checks whether C1 contains a number. If it does then the WORKDAY formula is activated, otherwise the cell remains blank

**LongShanks** · 05-10-2010, 09:59 AM

thanks,

i've pasted in your formula but it does not appear to be working as all the cells are all blank, whether there is a number in column C or not

**daddylonglegs** · 05-10-2010, 10:27 AM

That could happen if you have a number formatted as text. Try this version

=IF(C1="","",IF(ISNUMBER(C1+0),WORKDAY(TODAY(),C1),""))

**LongShanks** · 05-10-2010, 10:48 AM

That's perfect!!

I'm learning everyday, thank you very much

Insert a future date based on a number in another cell

LinkBack

Thread Tools

Rate This Thread

Display

Insert a future date based on a number in another cell

Re: Insert a future date based on a number in another cell

Re: Insert a future date based on a number in another cell

Re: Insert a future date based on a number in another cell

Re: Insert a future date based on a number in another cell

Re: Insert a future date based on a number in another cell

Re: Insert a future date based on a number in another cell

Re: Insert a future date based on a number in another cell

Re: Insert a future date based on a number in another cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions