Our new Indie Games subforum is now open for business in G&T. Go and check it out, you might land a code for a free game. If you're developing an indie game and want to post about it, follow these directions. If you don't, he'll break your legs! Hahaha! Seriously though.
Our rules have been updated and given their own forum. Go and look at them! They are nice, and there may be new ones that you didn't know about! Hooray for rules! Hooray for The System! Hooray for Conforming!

# wanting excel if function to return a date

Registered User regular
Tried setting this up but i couldn't get it to work right

when i am doing a multi-day experiment, i sometimes shorthand a column to designate what day order i did something on i.e. Monday is day 1, tuesday is day 2, etc

so column A will have that number in it.

on a different column i want it to look at column A and then return the actual date of whenever it was done

i tried it using nested if statements, here is what i had in

=if(A1=1, 6/9/11,if(A1=2, 6/7/11, if(A1=3, 6/7/11)))

it kept coming up as false. If i put the actual number for the date it came up with a #value error
also if i just use one if statement, it returned 1/0/00

it stumped me and figure there are probably people on here way better at excel then me

mts on

## Posts

• Registered User regular
mts wrote: »
Tried setting this up but i couldn't get it to work right

when i am doing a multi-day experiment, i sometimes shorthand a column to designate what day order i did something on i.e. Monday is day 1, tuesday is day 2, etc

so column A will have that number in it.

on a different column i want it to look at column A and then return the actual date of whenever it was done

i tried it using nested if statements, here is what i had in

=if(A1=1, 6/9/11,if(A1=2, 6/7/11, if(A1=3, 6/7/11)))

it kept coming up as false. If i put the actual number for the date it came up with a #value error
also if i just use one if statement, it returned 1/0/00

it stumped me and figure there are probably people on here way better at excel then me

=if(A1=1, "6/9/11",if(A1=2, "6/7/11", if(A1=3, "6/7/11")))

Nvm. You just need quotes around the dates.

• Registered User regular
I've never used if statements in excel, but it looks like the syntax is (condition, result if true, result if false)

Do you maybe need a result if false for that third nested statement?

edit: Howard's probably right

• Way out in the water See it swimmin'?Registered User regular
try single quotes around the dates

#FreeThan
#FreeScheck
#FreeSKFM
• Registered User regular
=if(A1=1, "6/9/11",if(A1=2, "6/7/11", if(A1=3, "6/7/11")))

Nvm. You just need quotes around the dates.
well that did it. I could have sworn that i tried the quotes around the dates. nice that it was a simple thing

• Registered User regular
Without the quotes, Excel is trying to actually do the math of 6/7/11 and so on. Whereas with the quotes, it's treating 6/7/11 as one text item and doesn't try to do "anything" to it.

You may also want to add some specific output for if it's none of those options; as it's written, it will just produce FALSE. That may be all you want it to do, though.

• Registered User regular
Try and avoid hard-coding things like dates in your formulas. You'd be better off using vlookup.

General explanation assuming you know vlookup - create a table on a seperate sheet with two columns. Column A contains numbers 1 - 7 in cells A1 through A7. Column B contains the dates. Enter the starting date for the week in B1 in plain text. In B2, enter =B1+1. Drag that down through B7. For changes in weeks, repopulate the date in B1 to get new dates. Now for the formula you posted, assuming you had typed that in b1, and assuming your new table you entered is on Sheet2, replace it with
```=vlookup(a1,Sheet2!\$A\$1:\$B\$7,2,0)
```
. Drag that down to fill. Voila.

Also, if you do stick with your original formula, though you've already solved it, it's generally poor form to leave an IF statement hanging without an ELSE. In your example the last IF will error if the number is not 1 2 or 3, so a better formula would be =if(A1=1, "6/9/11",if(A1=2, "6/7/11", if(A1=3, "6/7/11","Outside Range"))) or something other descriptive text.

Guys? Hay guys?