
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Date, Date#, and AddMonths Question
I am trying to do the following and am unable to figure it out. Does anyone know how I can achieve this?
Goal
I have 2 fields: Year and Month_Nbr. In the end, I want them concatenated into the format 'yyyymm'. However, I need it to display 1 month prior to its value.
For example, 201902 would display as 201901; and 201901 would display as 201812; meaning the year would also need to decrease by 1 if the month is January.
This is the formula I came up with, which doesn't work because it reduces the year by 1, not the month.
Date(AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm'),-1),'yyyymm')
These are the steps I took:
- Started with a Year field and a Month_Nbr field
- Converted the month to include leading zero
- Num(Month_Nbr,'00')
- Concatenated the year field and month field
- Num(Year) & Num(Month_Nbr,'00')
- Num(Year) & Num(Month_Nbr,'00')
- Convert the concatenated field to 'yyyymm'
- Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm')
- Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm')
- Subtract a month
- AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm'),-1)
- AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm'),-1)
- Returned to 'yyyymm' format
- Date(AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm'),-1),'yyyymm')
Result: 201902 becomes 201802
Desired: 201902 should become 201901
Any guidance is appreciated, thank you.
- Tags:
- date
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think you might have missed the trick by using mm instead of MM. M is for Month and m is for minutes

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
Date(AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'), 'YYYYMM'), -1), 'YYYYMM')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I think you might have missed the trick by using mm instead of MM. M is for Month and m is for minutes

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you, that did it!
