Datevalue

[darchaf]

Hey was wondering if it would be possible to do the following:

Take this formula:

=IF(NOW()<=DATEVALUE("9/12/2012"),"",IF(A!E9=TRUE,"P","N")) 

and make it so the DATEVALUE day (in this case 12) came from another cell

Ex) a formula that looked like this:

=IF(NOW()<=DATEVALUE("9/=e7/2012"),"",IF(A!E9=TRUE,"P","N")) 

where =e7 registered in the formula as being 12... is there anyway to do this?

[jeffreybrown]

Give this a try

DATE(2012,9,DAY(E7))

[FDibbins]

try using indirect()

(im working on the syntax)

[darchaf]

try using indirect()

(im working on the syntax)

how do I use indirect?

[darchaf]

Kind of works but not properly in the formula. In the first formula I need a DATEVALUE command otherwise it doesn't work properly

[jeffreybrown]

What is this?

A!E9=TRUE

[darchaf]

It refers to cell E9 on worksheet A. If cell E9 says TRUE then it'll post a P if it says FALSE it'll post an N... cell E9 is linked to a checkbox hence the True n false

[FDibbins]

sorry, use this...
=IF(NOW()<=DATEVALUE("9/"&E7&"/2012"),"",IF(Sheet2!E9=TRUE,"P","N"))

[darchaf]

sorry, use this...
=IF(NOW()<=DATEVALUE("9/"&E7&"/2012"),"",IF(Sheet2!E9=TRUE,"P","N"))

Excel tells me there's an error in the value

[FDibbins]

i had to change your sheet name. change it back to A

[darchaf]

i did lol thats when it gives me that error

[FDibbins]

it worked for me. upload a sample workbook please

[darchaf]

here's a quick sample

[FDibbins]

aahh you didnt mention that the "12" was already in date format. try this...

=IF(NOW()<=DATEVALUE("9/"&DAY(E7)&"/2012"),"",IF(A!E8=TRUE,"P","N"))

[darchaf]

that works great for the days! One last thing on this topic; is there any way to hook it up so it also picks the months and the years off of seperate cells? (months from E6 and years from E5)?

[darchaf]

Alright I got a complete fix that works great :

=IF(NOW()<=DATEVALUE(D6&"/"&DAY(D7)&""),"",IF(A!D8=TRUE,"P","N")) 

Thanks to everyone who helped

[oeldere]

Try this one.

=IF(NOW()<=DATEVALUE(&month(e6)&"/"&DAY(E7)&"/"&year(e5),"",IF(A!E8=TRUE,"P","N"))

I noticed you used merged cells. I advice you not to work with that; it get you in trouble sooner or later.

[darchaf]

@ oeldere

I noticed you used merged cells. I advice you not to work with that; it get you in trouble sooner or later.[/QUOTE]


Your formula works for reading the month and year however it screws up the DATEVALUE command (it's supposed to display nothing if it's before that date, and instead it displays an N)

[darchaf]

=IF(NOW()<=DATEVALUE(""&MONTH($D$6)&"/"&DAY($D$7)&"/"&YEAR($D$5)&""),"",IF(A!D8=TRUE,"P","N")) 

This is the formula I was using since yours also contained several mistakes

When I tested out the formula (by removing the month command and replacing it with a number), it worked. Looks like it is the MONTH command that isn't working properly...

[jeffreybrown]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[FDibbins]

do it the same way, but use year() and month()