Need to add 1 to the formula

[sachi1982]

hi All ,

could you kindly help me on this formula below. what i want to do is to check the G4 month value with todays month +1 month (meaning this should be 11 since we are in October). How can i do it in the formula. the current formula would not work.

please help.


This is what i thought the formula be but clearly it is wrong.

=IF(TEXT(G4,"mm")>TEXT(TODAY(),"mm")+1,"No Action Required","Check"

Thanks,
s

 

[mrshl9898]

=TEXT(TODAY(),"mm")+1 does indeed equal 11.

Perhaps your error is you are comparing Text instead of Numbers. Try.

=IF(NUMBERVALUE(TEXT(G4,"mm"))>NUMBERVALUE(TEXT(TODAY(),"mm")+1),"No Action Required","Check")

 

[Trebor76]

Hi sachi1982,

If the entry in cell G4 is actually a date and not a string that looks like a date, you shouldn't use the TEXT function in this case as it returns strings which can cause calculation issues.

See if this adapted formula does the trick:

=IF(MONTH(G4)>MONTH(TODAY())+1,"No Action Required","Check")

Regards,

Robert

 

[sachi1982]

Thanks yes it works but it convert text to numbers. is there a way that i could do that without changing the format type.

 

[sachi1982]

@Trebor76
the SAP report comes as a text file. so thats why i did it that way. thoughts.

 

[Trebor76]

Does this formula...

=TEXT(MONTH(G4),"mm")

...actually return the month for the entry in cell G4?

 

[sachi1982]

it does not. Dont worry I think i will go with mrshl9898 formula. it works.

 

[Trebor76]

it does not.

So that says the entry in G4 is actually a string not a date.

Dont worry I think i will go with mrshl9898 formula. it works.

Good stuff

 

[steve the fish]

That formula isnt going to behave as expected at the turn of the year. Would this work?

=IF(G4>EOMONTH(TODAY(),1),"No action required","Check")