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
mts
Registered User regular
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
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
=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.
Do you maybe need a result if false for that third nested statement?
edit: Howard's probably right
#FreeScheck
#FreeSKFM
[Edit]
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.
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 . 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.