How to find the Fiscal Quarter End Date?

[Nethaji_5]

Hello all,

Good Day!

I have the requirement to find the aging as of Fiscal Quarter End, Could you help me to find the solution.

I tried the below formula but it showing quarter-end as March but my actual quarter-end was April. Please let me know your comments. 

DateTimeTrim(Left([Due Date],5) +
PadLeft(ToString(Ceil(DateTimeMonth([Due Date])/3)*3),2, '0')
+ '-01', "lastofmonth")

[pedrodrfaria]

Hi @Nethaji_5 

Can you please explain the logic you are trying to apply?

[Nethaji_5]

Hi @pedrodrfaria 

My Logic is very simple. I'm trying to find the aging "as of today" and the "end of the Fiscal Quarter Date" with my Due-Date.

Please refer to the below newly added columns "Age" and "Age EOQ". 

[pedrodrfaria]

Hi @Nethaji_5 

I attached an example below to show how you can calculate these two calculations.

Please let me know if I misunderstood your ask.

Pedro.

[Nethaji_5]

Hi @pedrodrfaria 

Thanks for your reply,

We don't have an EOQ date in the source data, could you help me to find the date by using the formula then we can easily find the aging. 

E.g

1. Based on the Due Date (2021-03-10)  we need to find EOQ, that is (2021-04-30).

2. Like for Due Date (2021-05-01)  we need to find EOQ, that is (2021-07-31) and so on. 

My Fiscal Quarter: October, January, April, July

[pedrodrfaria · Accepted solution]

HI @Nethaji_5 

See attached for the updated WF.

[Nethaji_5 · Accepted solution]

Hi @pedrodrfaria 

Thank you so much for your help. 

Here is my solution based on your formula,

1. Finding the Fiscal Quarter

IF
DateTimeFormat(DateTimeToday(),"%B") = "February" OR
DateTimeFormat(DateTimeToday(),"%B") = "March" OR
DateTimeFormat(DateTimeToday(),"%B") = "April"

THEN "April"

ELSEIF
DateTimeFormat(DateTimeToday(),"%B") = "May" OR
DateTimeFormat(DateTimeToday(),"%B") = "June" OR
DateTimeFormat(DateTimeToday(),"%B") = "July"

THEN "July"

ELSEIF
DateTimeFormat(DateTimeToday(),"%B") = "August" OR
DateTimeFormat(DateTimeToday(),"%B") = "September" OR
DateTimeFormat(DateTimeToday(),"%B") = "October"

THEN "October"

ELSEIF
DateTimeFormat(DateTimeToday(),"%B") = "November" OR
DateTimeFormat(DateTimeToday(),"%B") = "December" OR
DateTimeFormat(DateTimeToday(),"%B") = "January"

THEN "January"

ELSE ""

ENDIF

2. EOQ Date Parse

DateTimeAdd(DateTimeParse([EOQ Month],"%B"),DateTimeYear(DateTimeToday())-DateTimeYear(DateTimeParse([EOQ Month],"%B")),'years')

3. EOQ End Date

datetimeadd(DateTimeAdd([EOQ Start Date],1,'month'),-1,'day')

4. Date Difference with my Due Date

DateTimeDiff([EOQ End Date],[Due Date],"days")

Output

I'm sorry I should not share my original data sources and this works for me.

[pedrodrfaria]

@Nethaji_5 

I'm not sure I follow, do you still have any questions or were you able to get it done?

If you were able to complete the task and if my answers helped you, please remember to assign a solution to the answer(s) that helped you.

Pedro.