Hi All,
I am needing to create a formula that will will return a month based on the date in another column.
Currently I have FORMAT_DATE(Today(),“MMMM”) which works great, except our revenue month starts on the 11th and ends on the 10th.
How do I compensate for this unusual month start and end in my formula?
You need a conditional formula for this
Try the following using the column Date in this example. Change the column name to the column in your board
IF(Day({Date})>10,FORMAT_DATE({Date},“MMMM”),
IF(MONTH({Date})=1,“December”,FORMAT_DATE(ADD_DAYS({Date},-20),“MMMM”))
)
Thank you so much! This works perfectly for every day but the 1st lol. Do you know of any workaround for that?
Hi Crystal
Do you mean the 1st of the month? Because its working when I tried it on my board
Could you share the date that its not working for and also an image of the board?
Hi Dipali,
Thank you so much for helping me with this. Yes, it is the first of the month that is giving me trouble. If I change it to the 2nd, it works but when it is the 1st, it returns the same month.
I have included a picture for you.
Can you paste the formula you used here. This seems to work correctly in my board but I may be missing something
Sure, here it is.
IF(Day({ACM Ship Date})>10,FORMAT_DATE({ACM Ship Date},“MMMM”),
IF(MONTH({ACM Ship Date})=1,“December”,FORMAT_DATE(ADD_DAYS({ACM Ship Date},-20),“MMMM”))
)
I modified the formula so this should work
IF(Day({Date})>10, FORMAT_DATE({Date},“MMMM”),if(Month({Date})=1,“December”,FORMAT_DATE(Date(YEAR({Date}),Month({Date})-1,1),“MMMM”)))
Basically I have calculated the date in the last bit so that it is the 1st of the previous month and then extracted the month from it.
Here is the thread I found pertaining to this exact problem.
I’m not sure what is happening but when I copy your formulas I have to change the “” symbols for it to not show as an illegal formula. So I’m not sure if that is the cause but this one still does not return the correct month. I have included a snippet of the formulas so that you can see the shape difference.
This new formula does however, help with the 10th of the month; I didn’t realize I was having that problem as well with the first formula you provided.
Hi Crystal
I too have had issues while copying things from the community chat into the formula. I have tried pasting into notepad and then copying the formula over. Sometimes it works, more often you have to build the formula again - quite annoying
I cannot see why you are facing this issue with the 1st of December. It works fine on my board which makes no sense really
Would you be open to sharing your board with me? If so my email is anumeghaCSL@gmail.com
The instructions to share a board with an external person are here - https://support.monday.com/hc/en-us/articles/115005309925-Shareable-Boards
It may be prudent to make a duplicate of the board and share that with me rather than changing the type for your original board
Amazing - Very good approach
I truly appreciate the time you have spent to help me figure this out.
I read in my online searches that it may have to do with time zones and the GMT line with regard to how computers to dates. If this is truly the issue, I am not sure of how to avoid it without simply changing our dates from the 1st to the 2nd. Which is not that huge of an issue in this case, just rather an annoyance.
I am on the west side of this line in the US. By chance, are you on the east side of it?
I will need to verify with our IT dept. about sharing outside of our company. We have very strict rules governing our IT systems.
Hi Crystal
I am in the UK.
The time zone should not matter as Monday usually deals with it in the background. However worth checking your settings.
Happy to help with this on a test board that has dummy data if that works for your IT team.
If you are looking for a more formal arrangement with an NDA in place then we can explore that as well.
Kind regards,
Dipali
Hi Dipali,
I have another unusual date situation. I need the same thing from above but this time on the 15th of the month, it changes to the next month. How do I modify this formula for this situation?
Hi Crystal,
Apologies for the delay in responding, I had to recreate the formulae as I had deleted the demo.
I cant see any issues with the calculation if the date is the 15th. I have set the formula on my board on the Start date and it works correctly - see image below.
If you are still facing the issue can you paste the formula and a screenshot?
Hi Dipali,
No worries, I am appreciative of your help. I was able to get the formula to work with the 15th as you have above. Unfortunately, the issue is that if it is, say ,Oct. 15th the month needs to read as Nov. This is the part I cannot figure out.
That is strange and an interesting problem. Will investigate further and get back to you
Kind regards,
Dipali
@catkins @DipaliPatankar I think the hard coding of the day = 1 in the formula is probably causing the weird behavior. Please see modified formula with the change in bold,
IF(DAY({Date})>10, FORMAT_DATE({Date},“MMMM”),
FORMAT_DATE(DATE(YEAR({Date}), MONTH({Date})-1, DAY({Date})),“MMMM”))
Works for me, hope it does for you all too.
Sorry, here is the formula as well. I forgot to attach it in my reply.
IF(Day({Planned Completion})>15, FORMAT_DATE({Planned Completion},“MMMM”),if(Month({Planned Completion})=1,“December”,FORMAT_DATE(Date(YEAR({Planned Completion}),Month({Planned Completion})+1,DAY({Planned Completion})),“MMMM”)))
