Date calculated from text field

[David_Little]

Hi everyone,

I am trying to find a way of extracting the end-of-month date based solely on a text field that shows the month. In the example below, I'm struggling in particular when the text month needs to sit in the following year compared to its "end date"

So line one as an example would need to show an "end billing date" of 31/05/2022

Hope that makes sense.

Thanks !

[DavidSkaife · Accepted solution]

Hi @David_Little 

Here is one way of doing it, EDIT: updated formula to account for the end date being in a subsequent year, if that were to happen:

[David_Little]

Hi @DavidSkaife unfortunately not. I mention in the description "I'm struggling in particular when the text month needs to sit in the following year compared to its End Date" (sorry if that wasn't clear)

[IraWatt · Accepted solution]

Hey @David_Little,

I tried to reduce the size of the formula using the date time tool. This formula checks if the End Billing Month is less than the End Date Month. If it is then it adds a year on:

The community has some quick and easy videos on formulas and the Formula Tool here Writing Expressions If your interested in learning more about the datetime functions there is a great cheat sheet Blog here: DateTime-Functions-Cheat-Sheet.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[DavidSkaife]

Hi @David_Little 

I've updated my original post to hopefully account for that

[OllieClarke]

Hey @David_Little 

Try this formula

datetimeadd(
datetimeadd(
DateTimeParse(
[End Billing Month]+' '+Tostring(
tonumber(Right([End Date],4))+1)
,'%B %Y')
,1,'month')
,-1,'day')

Which makes a date out of the month plus the end date year +1, then makes sure it is the end of that month

Hope that helps,

Ollie

[DataNath · Accepted solution]

Also had a crack at this with an admittedly horrible looking formula:

IF DateTimeMonth(DateTimeParse([Month],'%B')) < DateTimeMonth(DateTimeParse([End Date],'%d/%m/%Y')) THEN
DateTimeTrim(DateTimeParse(
[Month] + ' ' + ToString(DateTimeYear(DateTimeParse([End Date],'%d/%m/%Y'))+1),'%B %Y'),'lastofmonth') ELSE 
DateTimeTrim(DateTimeParse(
[Month] + ' ' + ToString(DateTimeYear(DateTimeParse([End Date],'%d/%m/%Y'))),'%B %Y'),'lastofmonth') ENDIF

[David_Little]

Thanks all !

[OllieClarke · Accepted solution]

Apologies @David_Little I misunderstood your problem

This (beast) of a formula gets you to where you want I believe

IF datetimemonth(datetimeparse([End Billing Month],'%B'))<
tonumber(substring([End Date],3,2))
//If needs to be following year
THEN datetimeadd(
datetimeadd(
DateTimeParse(
[End Billing Month]+' '+Tostring(
tonumber(Right([End Date],4))+1)
,'%B %Y')
,1,'month')
,-1,'day')
ELSE 
//otherwise same year
datetimeadd(
datetimeadd(
DateTimeParse(
[End Billing Month]+' '+Right([End Date],4)
,'%B %Y')
,1,'month')
,-1,'day')
ENDIF

So we first check to see if the end month is before the end date, and if so we do my previous formula, if not then we take the current year of the end date.

Hope that helps,

Ollie

[OllieClarke]

@DataNath I always forget about 'lastofmonth' 😶