if formula help

[plost33]

i am wanting an if formula that will look at a cell and if the date in that is in the current month then it should return "yes" and return "no" if that date is not in the current month.

example:

cell A1 = 4/21/2011

this if formula is in cell A2. it should return "no" because 4/21/2011 is not in the current month of June 2011.

hope someone can help.

 

[Aladin Akyurek]

aladin, that does not seem to work. my date in a1 is 5/24/2011. this formula should return 6/1/2011 because it is the first day of the following month.

This yields the first day date of today's date...

=IF(A1-DAY(A1)+1=TODAY()-DAY(TODAY())+1,"Yes",TODAY()-DAY(TODAY())+1)

This gives the first day date of the following month/year with respect to the date in A1...

=IF(A1-DAY(A1)+1=TODAY()-DAY(TODAY())+1,"Yes",DATE(YEAR(A1),MONTH(A1)+1,1))

 

[plost33]

Aladin, great. here is the final formula:

=IF(H2-DAY(H2)+1=TODAY()-DAY(TODAY())+1,H2,DATE(YEAR(H2),MONTH(H2)+1,1))



i have one last request: how can we make this only return a value if there is a value in cell H2? if H2 is blank then the formula should return a blank.

 

[Aladin Akyurek]

Aladin, great. here is the final formula:

=IF(H2-DAY(H2)+1=TODAY()-DAY(TODAY())+1,H2,DATE(YEAR(H2),MONTH(H2)+1,1))



i have one last request: how can we make this only return a value if there is a value in cell H2? if H2 is blank then the formula should return a blank.

Try...

=IF(N(H2),IF(H2-DAY(H2)+1=TODAY()-DAY(TODAY())+1,H2,DATE(YEAR(H2),MONTH(H2)+1,1)),"")

 

[plost33]

Aladin thanks so much. that works great.

 

[hellebore08]

this should work..

=IF(AND(MONTH(TODAY())<>MONTH(A1),YEAR(TODAY())=YEAR(A1)),MONTH(TODAY())&"/"&"1/"&YEAR(TODAY()),"")

added iserror for you to work on.. sorry for too many arguments.. =P

=IF(ISERROR(IF(AND(MONTH(TODAY())<>MONTH(A1),YEAR(TODAY())=YEAR(A1)),MONTH(TODAY())&"/"&"1/"&YEAR(TODAY()),"")),"",IF(AND(MONTH(TODAY())<>MONTH(A1),YEAR(TODAY())=YEAR(A1)),MONTH(TODAY())&"/"&"1/"&YEAR(TODAY()),""))

cheers!

-e.rgabrieldoronila

 

[Aladin Akyurek]

Aladin thanks so much. that works great.

You are welcome. Thanks for providing feedback.