Date calculated from text field

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 !

Workflow

Tips and Tricks

Transformation

Data Investigation

Accepted answers

davidskaife

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:

Dates.yxmd

IraWatt

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

Workflow.yxmd

DataNath

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

OllieClarke

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

enddate.yxmd

All comments

davidskaife

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:

Dates.yxmd

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

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:

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Workflow.yxmd

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

enddate.yxmd

DataNath

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

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

enddate.yxmd

OllieClarke

@DataNath I always forget about 'lastofmonth' 😶

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335