I may have missed it but I have searched the discussions and have not been able to find a calculation that will allow me to extract the month and year from a date field. Any assistance would be greatly appreciated.
Month ( )
MonthName ( )
Year ( )
FMP has built-in functions for extracting values from a date.
Using "date_field" as the source:
Year ( date_field )
Month ( date_field )
Day ( date_field )
You can use them independently or together to create specific formats.
For example, formatting Month with a leading zero and showing year as four characters.
Right ( "00" & Month ( date_field ) ; 2 ) & "/" & Year ( date_field )
Note: in order to show leading zeros the results must be text. A "number" result does not have leading zeros.
Thank you. Couldn't get the result I wanted from Functions so I must have done something wrong. I used the formatting and got what I needed to be able to move forward. I'm sure that is a better way to get the information but my FM skills are not where I would like them to be. My issue is that I have a date that is an exact date of enrollment. Quarterly reports need to be done based on the month of enrollment (regardless of the day) so I need to have the day be the first of the month , e.g., 2/13/2017 would be 2/1/2017. That way I can set calculation fields that will show the date that the next report is due. Any suggestion on an easier way?
Keep in mind that the built-in functions work with specific date formats. I've run into issues in the past with trying to get date information from an ISO-formated date, and had to write a function to parse it.
The built in functions work fine for a date field type, but if you have a text field type and are capturing ISO-formatted dates (ie: 2017-04-17), you may have to parse it yourself (ie: Left(your_date-field ; 4) to get the year, etc.).
Let ( [
F1 = Table::EnrollmentDate ;
F2 = Date ( Month ( F1 ) ; 1 ; Year ( F1 ) )
Thank you. That gave me the exact date I was looking for. Is there a way to use this date in a calculation that will 3 months forward without using 90 days? When 90 days are used the day changes from the first depending on the number of days in a month. May not be possible, but thought I would ask
F2 = Date ( Month ( F1 ) + 3 ; 1 ; Year ( F1 ) )
Many thanks! I really appreciate everyone's help with this.
I do something similar, but the result is like this:
That way anywhere this is used makes the field sortable that is Alpha-sortable, but also becomes chronologically sorted.
It's easy to use for sorting/grouping by month (and year).
The value produced here in the field expre that I posted is also sortable and can be formatted with date formatting options in the inspector.
Yes! similar to Taylor's reply.
A date with the first day of the month:
are "legal" dates and handy for sorting and grouping, too.
I've also run into places where I need two fields:
dt_m = auto-enter: Month(myDate)
dt_y = auto-enter: Year(myDate)
In cases where needing to group by month (regardless of year)!
All these tips should help OP see that FMP has some good functions and functionality (formatting) to work with dates.
yeah, it better be "similar to his" as I copied his expression and then edited it to answer a follow up question!
Retrieving data ...