MonthName problem

[sayanpwc]

Hi guyz,

Facing a problem with my monthname field (MONTHS).

My requirement is to when activate the sheet this MONTHS field should always be selected < current month.

For ex: if current month is May, value in MONTHS field should be selected from Jan,Feb,Mar,Apr.

The values in MONTHS fields are like January,February,March ....

I dont have any numeric field corresponding to this MONTHS field. without doing anything in the script end can we achieve this in trigger?

How can I do that??  Please assist. It is urgent,

[shansundar]

Yes you can.

=If(Month(Date#(MONTHS, 'MMMM'))<Month(Today()), Date#(MONTHS, 'MMMM'))

Thanks,

Shan S

[sayanpwc]

Its not working. I have put on activate sheet trigger ' select in filed' MONTHS field and under search string

=If(Month(Date#(MONTHS, 'MMMM'))<Month(Today()), Date#(MONTHS, 'MMMM'))

but its not working. Please help what or where should i write to get all the months selected while activating that sheet.

[Miguel_Angel_Baeyens]

MONTHS alone should not work, because December 2017 is a date smaller than May 2018, yet it will not display, because December is higher month than May.

You need to use a date field instead and the function AddMonths() as the filter for the selections. For example:

AddMonths(Today(), -6) will return the date of today 6 months back.

Or you will have to create in the script a numeric value for each distinct month you have in the data model, so December 2017 is month 1, January 2018 is month 2 and so on.

[sayanpwc]

yes I know that. I wanted to achieve it thorugh front end using the MONTHS field only that contains string values like January,February... so that is not possible right?

[Miguel_Angel_Baeyens]

I don't see how if you only have a string. You still could use a Pick() and Match() combination to get numeric values but how do you then distinguish the year? Unless this is an application for one year only?

[sayanpwc]

Yes forget about the year as of now. Its only about one year month

[Miguel_Angel_Baeyens]

Then use a Pick() or Match() or both:

Match(Date(Today(), 'MMMM'), 'Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember')

Will return the numeric value for the month, and

Match(MONTHS, 'Januar','Februar','März','April','Mai','Juni','Juli','August','September','Oktober','November','Dezember')

Will return the numeric value of the month in that field, assuming they are written with the same capitalization.

You can then compare and select when one is higher or lower than the other.

[sayanpwc]

I want this in trigger while activating sheet. Where and how to write this can u pls tell me? Thanks

[sayanpwc]

and also want to do in the front end everything not at the script level