Week Commencing, containing the 1st July, of the current year

[RBF]

Hi all

I'm looking for a formula that will give me the week commencing date, of the week which contained the 1st July (our fiscal year start) of the current fiscal year:

Ideally output in yyyy-mm-dd format

If I run it today it would give me 2022-06-27

if I ran it in January next year it would gve me 2022-06-27

But if I ran it on 2nd July next year it would give me 2023-06-29

[IraWatt · Accepted solution]

Hey @RBF,

Here is one way of doing it:

I tryed to split up my calculations so first I find out which year we need to look at based of the month number. Next I convert that yeas 1st July to its day of the month. Then I subtract the number of days to get to the start of that week. Interestingly I believe the 2023 1st of July starts on the 26th not the 29th.

The community has some quick and easy videos on formulas and the Formula Tool here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Writing%20...

If your interested in learning more about the datetime functions there is a great cheat sheet Blog here: https://community.alteryx.com/t5/Engine-Works/DateTime-Functions-Cheat-Sheet/ba-p/844353

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[grazitti_sapna]

One way of doing this is using different table to maintain fiscal year entries and date range and second method is using formula tool and using IF ELSE statement to check if current date lies between range and assign fiscal year. Please find the workflow and let me know if it was helpful.

[RBF]

Yes, that worked great, thanks for fast reply (also, you are correct, the date should be the 26th )

[Christina_H · Accepted solution]

Here's a slightly simplified formula, without having to list all the days of the week

[IraWatt]

Great to hear @RBF ! I would definitely check  out @Christina_H answer as its a lot cleaner solution then mine 😅 (you can green tick multiple solutions). Have a good week !