Convert Dates to Fiscal Periods in Excel - Easy Formula
HTML-код
- Опубликовано: 3 авг 2024
- Use this easy formula to convert your dates into their fiscal quarters and year. Download the Excel file here: www.myonlinetraininghub.com/e...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
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.
So pleased it will be useful to you :-)
Much better than what I did, I’ll replace my workbooks’ formula with this function. Thank you Mynda.
Awesome to hear!
Thanks Mynda! Learning new things from you is amazing!
Wonderful to hear :-)
Very very good. A bit quick for me - had to watch a couple of times but it was worth it. Thank you.
Glad it was worth your time, Joshua! Use the speed controls on the video settings to slow them down if required.
Thank you so much for this tutorial. You made it sound simple.!!
Glad it was helpful!
Hi Mynda!Great Tutorial Especially The Example With The Choose Function...Thank You :)
Thanks for watching, Darryl! Glad you liked the CHOOSE trick :-)
I don't ever use CHOOSE, so this was a great example of how to use it, thanks Mynda!
So pleased it was worth your time, Chris!
This is the one I needed it today! Thank u!
So glad, Rina!
Super useful techniques and perfect timing too. We are doing our annual closing and this would be of great help.
Glad it was helpful!
Hi Mynda.. great video! CHOOSE is handy in so many situations. Thanks for all the other examples too.. very useful! Thumbs up!!
Thanks so much, Wayne!
As always, another great tutorial - thank you very much.
Glad you enjoyed it, Fred!
Thank you for the video Mynda. Very useful!!!
Glad it was helpful, Ivan!
CHOOSE function was awesome, thanks.
:-) glad you liked it!
Thank you for another super useful tutorial!
Glad you enjoyed it, Kenneth!
Thank you for this! I now have an idea on how to determine the billing month of a date. Our billing month is every 26th of last month to 25th of the month and I've struggled to find a formula for this. I usually do it manually but now I have an idea to convert it using a formula.
So pleased to hear you found some techniques you can use :-)
Very useful and explained well. Thanks for this tip.
Glad you'll be able to use it, Alison :-)
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.
Great to hear it'll be useful, Murat :-)
Thank you very much
Always I learn something new in your videos
Pleased to hear that, Anji!
Thanks Mynda. That was interesting.
Glad you enjoyed it, John!
Good one. Thank you. Smart way of using formulas to suit our needs
My pleasure :-)
Hi is there a way to use coloured text or shaded cells in formulas. I need to exclude those cells in formula. Appreciate if you have a solution
You Are a ROCKSTAR.... BLESSINGS..
Thanks, Ashish!
another amazing tip ---THANK YOU
You are so welcome! 🙏😊
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)
Super useful tutorial.. thanks for sharing
So pleased you can make use of it, Alex!
thanks you for your knowledge sharing it helps me a lot
Glad to hear that!
Hi Mynda, my calendar looks really profesional 😎
So pleased it was useful to you, Arturo :-)
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.
Thanks for sharing, Jason. Nice twist to use both ideas in one formula!
It's awesome .....Thanks a ton 🙏🙏
My pleasure, Dhun :-)
Brilliant, as always
Thanks so much :-)
Very useful thanks for sharing
My pleasure, Ali! 😊
Thank you!
You're welcome!
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.
Thanks, Sheila. I'll add it to the list!
Hello Mynda: from the pampas Argentina go my thanks for sharing so much knowledge. People like you are essential. Thanks again.
Thanks so much, Martin! Great to know I can help.
This helped! Thank you!
Great to hear!
Thank you for your videos, I hope your channel grows! It’s really really helpful!
Thanks so much! Please spread the word about our channel.
great job! :)
Thanks, Ali! 😊
You are really genius how to use some new function.
Thanks so much, Nitesh :-)
Please make a video on using the Excel Forecast.ets functions and how they work
Will add it to the list!
Hi Mynda, in the UK I use this formula to show a slightly different variation for fiscal year.
Assuming the date is in cell A2: =LEFT(YEAR(A2)-(MONTH(A2)3),2)
Nice! Thanks for sharing, Paul. The LEFT function is redundant though. This returns the same result: =YEAR(A2)-(MONTH(A2)3),2)
@@MyOnlineTrainingHub 😀 Not sure where that came from!
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 post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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!
If you require non-standard quarterly groupings then you need to add a column to your source data that classifies each row into the quarter you want. A PivotTable grouping will always be based on calendar quarters.
Great explanation - can you do this with Week Numbers as well?
You'd have to convert the week numbers to dates, then covert those dates to fiscal periods.
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.
Nice alternative 👍
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?
Glad it was helpful. You can use the IF function to handle blank dates. e.g. =IF( your formula = 0, "", your forumla)
Thanks Mynda for the video. Any idea how the same can be done in case of 'Timelines' for a Pivot table/chart...?
Hi Mandar, Timelines create their own groupings, you can't specify them based on fiscal periods, sorry.
@@MyOnlineTrainingHub Appreciate. Thnx :-)
Great video, now if we can just have orgs all use the same fiscal periods :-)
:-D yes!
I use pivot tables to analyze date by year and month and we do a fiscal year starting in April ... how do I analyze our fiscal year using the grouping fester in the pivot table as you mentioned?
I said that in relation to the first example where your fiscal period matches the calendar year. if your fiscal year starts in April then you can't use the PivotTable grouping, which is why you need the second technique.
Thanks for this. Now subscribed to your tutorials. How do I get the fiscal year to appear as 2018/19 instead of just 2018?
Where J2 contains your date:
=IF(MONTH(J2)
@@MyOnlineTrainingHub Thank you so much!
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.
You can use this formula: =IF(MONTH([@Date])
@@MyOnlineTrainingHub Very nice of you, Mam. Its working fine. Thanks a lot.
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
Thanks, Zahid! Not sure what you mean with your issue. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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
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?
Hi Mynda, thanx for sharing. May i ask, why divided by 3? =ROUNDUP(MONTH)/3,0)
Thank-You very much
aaah.. i got it! because a quarter has 3 months..how silly i couldnt figure those
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!
Hi Lana, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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.
Sounds like you have specific week numbers as opposed to calendar week numbers. In which case you can use the lookup table approach as shown here for fiscal periods.
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?
Hi Naima, you can use the DATEVALUE function to convert the text to dates. =DATEVALUE(cell containing text date)
@@MyOnlineTrainingHub thank you so much Mynda for your time. It worked.
Thanks for the tutorial.
What if the fiscal year starts from 3rd of JAN 2021 and ends at 2nd JAN 2022?
Then you should use the last example where you have a lookup table.
And for us in the UK - tax year from 6 April to 5 April? Like the video!
You need to use the same technique as the 4-5-4 calendar where you specify the date ranges for each quarter.
if I have a column values like FY22Q1 and want to convert into mm/dd/yyyy format ?
How will you know what month it relates to seeing a quarter is 3 months.
@@MyOnlineTrainingHub lets assume it will be the first day of the first month
Hi Debayan, there are a few ways to tackle this. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum Also let us know what version of Excel you're using, so we know what functions you have access to.
What exactly prevents vlookup from looking for the date in a wrong column (qtr end) ?
The date you're looking up must be the first column referenced in the table_array argument. As long as you do this, it can't lookup the wrong column. Here is a video tutorial on VLOOKUP that explains it further: ruclips.net/video/4-5-TBhOP6Q/видео.html
One more Question, I have tried roundup function, but unable to get Q4, formula tried =Roundup (Month(@date)/4,0)
The ROUNDUP example is only for converting months to quarters where your financial year is the same as the calendar year. If your fiscal periods are different to the calendar year then you need to use the other formula.
is it possible to have this show WEEK based on fiscal year. eg 5 July = week 1 ?
You can use the WEEKNUM function to return the week number for a date.
@@MyOnlineTrainingHub Excel returned week 4.. when using say 17 Jan 2022.. where as this should be week 30 (based on June fiscal year). Is there a way to combine this with CHOOSE formula you mentioned?
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 🌹🌹
Hi Mohammad, Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub , thank you i send the file to forum 🙏🙏🙏
Hi, I have some doubts, if I have 4 qtr and my qtr starts from Apr- as Qtr1, then how will be the formula.
Replace the 7 with 4 in the formula.
Thanks alot mynda,for your timely response....
still not getting this roundup function and i dont know why?
Hi Tolulope, If the month number is 1 and you divide it by 3 you get .3333' and when you round up .3333' you get 1. i.e. quarter 1. Month 2 divided by 3 returns .6666' and when rounded up returns 1 i.e. quarter 1. And so on. You might like to use the Evaluate Formula tool on the Formulas tab to see how the formula evaluates. Mynda
@@MyOnlineTrainingHub I understand the meaning of the roundup formula. What I did not get initially was how to insert [@Date] in the formula, but later on, when I picked the cell that contained the date, I realized it worked well. But still wondering why @Date did not work in the formula. I wondered if it is the excel version or maybe I just actually needed to pick the date cell in the formula. Thank you sooo much for your guidance. Stay blessed. Amen
I tried this and it’s not working for me. Did you have to define the date to @Date?
The @Date is a structured reference to a cell in an Excel Table. I formatted my data in an Excel Table, as explained here: www.myonlinetraininghub.com/excel-tables
Please I want a course for excel and one tshirt
😁I can help with the course here: www.myonlinetraininghub.com/ The t-shirt is no longer available from Microsoft, which is where I purchased mine, but I think you can get some copies of it from Redbubble.
'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)
Thanks for sharing. I try to avoid VBA at all costs unless it's the only option. The dreaded .xlsm extension is a showstopper for many organisations and UDFs are typically less efficient than built in functions.
@@MyOnlineTrainingHub You are right but UDF is much user-friendly and clean if there is no inbuilt Excel function for the required output.
I Can not get this to work it is returning the wrong year or says missing closing parenthesis..... Help!!!!!
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub I have added it
CHOOSE(YOU=TIME SAVER,LIFE SAVER) ... sorry ;P XD
🙏😁