Convert Dates to Fiscal Periods in Excel - Easy Formula

another amazing tip ---THANK YOU

Hi, Linda. In our Org We treat Apr19 to Mar20 as a complete fiscal year2019. How can we use formula on this ? Example date is on H2

Thank you for this video! It was very helpful. I used the choose formula in my project tracker to associate the proper quarter with the date of the project.
My question though is my blank date cells are defaulting the quarter cell to the first quarter. Anyway to have the quarter cell stay blank if the date cell is empty?

Please I want a course for excel and one tshirt

Great Solution! QUESTION on this. If my dataset does not have a date listed, I'm getting a 1900 value. Is there a way to right the formula that essentially says if date field is blank, return blank, otherwise follow this solution demonstrated above?

Thank you!

CHOOSE(YOU=TIME SAVER,LIFE SAVER) ... sorry ;P XD

This helped! Thank you!

Hi Thank you , question i have pms file i want to know is there any way to excel calculate by own and find 3rd date between start and finish date with formula and gives best match date between 2 start and finish date , it's example that can expand for 1000 tasks
Imagine i have 3 tasks with 5 days duration start is 1/1/22 and finish is 1/12/22 so first task start 1 day and 3rd task start 1/7/22 now I want excel calculate and find best date between in 2 tasks that must be 1/4/22
Now is there anyway excel calculate and find this date, this is can use for 1000 tasks more or less that I need to find it
1000000 tnx if you help me 🌹🌹🌹🌹
Thank you 🌹🌹

if I have a column values like FY22Q1 and want to convert into mm/dd/yyyy format ?

if your fiscal year begins in May, you can use this to calculate FY, change the 5 to match your first fiscal month.
="FY"&RIGHT(YEAR(M2)+(MONTH(M2)>=5),2)

Our fiscal year starts on 10/1 of each year. How can I convert it to show fiscal year, period and week. example 10/1/2022 to look like FY22P1W1?

I tried this and it’s not working for me. Did you have to define the date to @Date?

is it possible to have this show WEEK based on fiscal year. eg 5 July = week 1 ?

I use the eomonth function with an offset of 6. Then It is easy to get the financial year. Use the divide by three trick to get the quarter.

Thanks, how do I get weeknum in a given quarter, I have 12 weeks every quarter, the formula should return me the week number based on the date.

You Are a ROCKSTAR.... BLESSINGS..

Hi Mynda, This is a great tutorial, Could you please solve my issue, Our payroll sheet starts on Thursday to Wednesday, but we enter timesheet hours every day, how I can group my weekend date, which is Wednesday. Let me know. Thanks

great job! :)

Thanks for the tutorial.
What if the fiscal year starts from 3rd of JAN 2021 and ends at 2nd JAN 2022?

Thanks for this. Now subscribed to your tutorials. How do I get the fiscal year to appear as 2018/19 instead of just 2018?

Great explanation - can you do this with Week Numbers as well?

How can I do the opposite? I have annual data (in fiscal years), but I also have other annual data in actual years. In case the fiscal year ends in May 31 2018. Would you match this data set to 2017 since the majority of months of the fiscal year is in 2017? Thank you for your help!

I Can not get this to work it is returning the wrong year or says missing closing parenthesis..... Help!!!!!

What exactly prevents vlookup from looking for the date in a wrong column (qtr end) ?

This is the one I needed it today! Thank u!

Thank you Mynda. The extraction of dates I get from our accounting system comes in this form " '12/09/2020" and Excel doesn't understand it as a date. Which formula I can use to remove the ' and get the dates in the proper form for Excel?

Thank U very much !! I was in very need of such a awesome formulas as I am accountant and it will be very helpful to me.
But, Mam, for Fiscal Year, we are following fashion such as 2018-19, 2019-20 etc. how can I do this ??? Plz. reply.

Hi There, thank you for always helping us out by sharing your videos with us. I have an issue here and I tried to use the choose function but I couldn't get the result I wanted. Our company is doing the weekly calculation for shipping. In 2019, the first day of the week happened to start on 12/31/2018. So, I ran a report from 12/31/2018 to 12/29/2019. When I group it on the pivot table, I group it as day and (number of days is 7). It looks perfect. However, when I group it in quarter, Excel took 12/31/2018 as 4th quarter. What should I do? Can you help me please? :-) Thank you very much!

Thank you very much
Always I learn something new in your videos

As always, another great tutorial - thank you very much.

Thank you for the video Mynda. Very useful!!!

Super useful tutorial.. thanks for sharing

Hello Mynda: from the pampas Argentina go my thanks for sharing so much knowledge. People like you are essential. Thanks again.

Hello Mynda,
Very simple and usefull solve. I am first time use a choose function. I use this method/function for many practice next time. Thank you sow much.

'One can also use below VBA UDF
Option Explicit
Function Quarter(dt As Variant, rootMnthNo As Variant) As Byte
'' dt is date range
''rootMnthNo is 1st month number of 1st qrtr
Dim mnth As Byte, nxtMnth As Byte
Dim cntr As Byte
mnth = Month(dt)
nxtMnth = rootMnthNo
For cntr = 1 To 12
Quarter = Application.WorksheetFunction.Ceiling(cntr, 3) / 3
If mnth = nxtMnth Then Exit Function

If nxtMnth = 12 Then
nxtMnth = 1
Else
nxtMnth = nxtMnth + 1
End If
Next cntr
End Function

'if fiscal year starts from Jan then use Quarter(DateRng,1) if from Apr then use Quarter(DateRng,4)

Hi Mynda, my calendar looks really profesional 😎

Great video, now if we can just have orgs all use the same fiscal periods :-)

This has got to be the neatest way that I have ever seen this issue managed in a calculation! In the past, I've always pushed to using Pivot Tables for summarising dates by quarters. With this method, the quarters can be readily defined for use anywhere.

Definitely one to add to the toolbox! :)

Thanks Mynda.

And for us in the UK - tax year from 6 April to 5 April? Like the video!

One more Question, I have tried roundup function, but unable to get Q4, formula tried =Roundup (Month(@date)/4,0)

Hi, I have some doubts, if I have 4 qtr and my qtr starts from Apr- as Qtr1, then how will be the formula.

Love your videos.

Since we are a manufacturing company, we use the epoch calendar for fiscal periods, making drastic differences in the closing day of the month. Can you please do a video or explain how to use formulas to extract quarterly data using the epoch calendar as a guide for the dates? Your help would be greatly appreciated as extracting data manually for fiscal periods adds considerable time to reporting. Grouping does not help in this situation when using pivot tables to compile data.

Thank you for another super useful tutorial!

A great use of CHOOSE and a great technique to put in the toolbox, thanks very much Mynda!

As a suggestion, what about rounding the MONTH/3 within CHOOSE in this case (combining your first and second techniques), which reduces the number of CHOOSE arguments required? For example:

=CHOOSE(ROUNDUP(MONTH(A1)/3,0),3,4,1,2)

I appreciate this doesn't work directly for financial years ending say February, but could be modified accordingly.

Good one. Thank you. Smart way of using formulas to suit our needs

Thank you so much for this tutorial. You made it sound simple.!!

Hi Mynda, thanx for sharing. May i ask, why divided by 3? =ROUNDUP(MONTH)/3,0)
Thank-You very much

Thanks Mynda for the video. Any idea how the same can be done in case of 'Timelines' for a Pivot table/chart...?