Much better than what I did, I’ll replace my workbooks’ formula with this function. Thank you Mynda.
@MyOnlineTrainingHub4 жыл бұрын
Awesome to hear!
@snicho4 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
So pleased it will be useful to you :-)
@chrism90374 жыл бұрын
I don't ever use CHOOSE, so this was a great example of how to use it, thanks Mynda!
@MyOnlineTrainingHub4 жыл бұрын
So pleased it was worth your time, Chris!
@muratucar2274 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
Great to hear it'll be useful, Murat :-)
@joshuamanampiu64894 жыл бұрын
Very very good. A bit quick for me - had to watch a couple of times but it was worth it. Thank you.
@MyOnlineTrainingHub4 жыл бұрын
Glad it was worth your time, Joshua! Use the speed controls on the video settings to slow them down if required.
@abdulhaseeb80274 жыл бұрын
Super useful techniques and perfect timing too. We are doing our annual closing and this would be of great help.
@MyOnlineTrainingHub4 жыл бұрын
Glad it was helpful!
@ivan29514 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
So pleased to hear you found some techniques you can use :-)
@Riri-qi2fu4 жыл бұрын
This is the one I needed it today! Thank u!
@MyOnlineTrainingHub4 жыл бұрын
So glad, Rina!
@markmagana81058 күн бұрын
Love your tutorials. They make my job much easier. How do you write the formula to leave the fiscal year cell blank if the date is missing?
@MyOnlineTrainingHub8 күн бұрын
Thank you! You can wrap it in an IF to test if the date is empty e.g. =IF(date cell = "", "", fiscal year formula)
@anjinoureddine15134 жыл бұрын
Thank you very much Always I learn something new in your videos
@MyOnlineTrainingHub4 жыл бұрын
Pleased to hear that, Anji!
@yashmalde67324 жыл бұрын
Thanks Mynda! Learning new things from you is amazing!
@MyOnlineTrainingHub4 жыл бұрын
Wonderful to hear :-)
@WebberJason4 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
Thanks for sharing, Jason. Nice twist to use both ideas in one formula!
@IvanCortinas_ES4 жыл бұрын
Thank you for the video Mynda. Very useful!!!
@MyOnlineTrainingHub4 жыл бұрын
Glad it was helpful, Ivan!
@FaysalEasyExcel4 жыл бұрын
CHOOSE function was awesome, thanks.
@MyOnlineTrainingHub4 жыл бұрын
:-) glad you liked it!
@MartinComolli4 жыл бұрын
Hello Mynda: from the pampas Argentina go my thanks for sharing so much knowledge. People like you are essential. Thanks again.
@MyOnlineTrainingHub4 жыл бұрын
Thanks so much, Martin! Great to know I can help.
@ashishm30363 жыл бұрын
You Are a ROCKSTAR.... BLESSINGS..
@MyOnlineTrainingHub3 жыл бұрын
Thanks, Ashish!
@paulsingleton60714 жыл бұрын
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)
@MyOnlineTrainingHub4 жыл бұрын
Nice! Thanks for sharing, Paul. The LEFT function is redundant though. This returns the same result: =YEAR(A2)-(MONTH(A2)3),2)
@paulsingleton60714 жыл бұрын
@@MyOnlineTrainingHub 😀 Not sure where that came from!
@MuslimIslam015 ай бұрын
thanks you for your knowledge sharing it helps me a lot
@MyOnlineTrainingHub5 ай бұрын
Glad to hear that!
@janakiramvvs39144 жыл бұрын
Good one. Thank you. Smart way of using formulas to suit our needs
@MyOnlineTrainingHub4 жыл бұрын
My pleasure :-)
@janakiramvvs39144 жыл бұрын
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
@wayneedmondson10654 жыл бұрын
Hi Mynda.. great video! CHOOSE is handy in so many situations. Thanks for all the other examples too.. very useful! Thumbs up!!
@MyOnlineTrainingHub4 жыл бұрын
Thanks so much, Wayne!
@b-b119 Жыл бұрын
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?
@MyOnlineTrainingHub Жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@Elbranto14 жыл бұрын
As always, another great tutorial - thank you very much.
@MyOnlineTrainingHub4 жыл бұрын
Glad you enjoyed it, Fred!
@ADfamily8110 ай бұрын
another amazing tip ---THANK YOU
@MyOnlineTrainingHub10 ай бұрын
You are so welcome! 🙏😊
@jackaubrey8184Ай бұрын
Just Brilliant! 🥰
@MyOnlineTrainingHubАй бұрын
Glad it was helpful! 🙏
@Terracotta-warriors_Sea4 жыл бұрын
Please make a video on using the Excel Forecast.ets functions and how they work
@MyOnlineTrainingHub4 жыл бұрын
Will add it to the list!
@AmberlyMiller-580Xen Жыл бұрын
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?
@MyOnlineTrainingHub Жыл бұрын
Glad it was helpful. You can use the IF function to handle blank dates. e.g. =IF( your formula = 0, "", your forumla)
@sheilasmith53894 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
Thanks, Sheila. I'll add it to the list!
@kaesuma4 жыл бұрын
Thank you so much for this tutorial. You made it sound simple.!!
@MyOnlineTrainingHub4 жыл бұрын
Glad it was helpful!
@alisonstewart43324 жыл бұрын
Very useful and explained well. Thanks for this tip.
@MyOnlineTrainingHub4 жыл бұрын
Glad you'll be able to use it, Alison :-)
@darrylmorgan4 жыл бұрын
Hi Mynda!Great Tutorial Especially The Example With The Choose Function...Thank You :)
@MyOnlineTrainingHub4 жыл бұрын
Thanks for watching, Darryl! Glad you liked the CHOOSE trick :-)
@arturoguzman52304 жыл бұрын
Hi Mynda, my calendar looks really profesional 😎
@MyOnlineTrainingHub4 жыл бұрын
So pleased it was useful to you, Arturo :-)
@mrkenwu14 жыл бұрын
Thank you for another super useful tutorial!
@MyOnlineTrainingHub4 жыл бұрын
Glad you enjoyed it, Kenneth!
@alexsonga47424 жыл бұрын
Super useful tutorial.. thanks for sharing
@MyOnlineTrainingHub4 жыл бұрын
So pleased you can make use of it, Alex!
@kevbelz2 жыл бұрын
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)
@dhunpagla38714 жыл бұрын
It's awesome .....Thanks a ton 🙏🙏
@MyOnlineTrainingHub4 жыл бұрын
My pleasure, Dhun :-)
@nitinshukla9144 жыл бұрын
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.
@MyOnlineTrainingHub4 жыл бұрын
You can use this formula: =IF(MONTH([@Date])
@nitinshukla9144 жыл бұрын
@@MyOnlineTrainingHub Very nice of you, Mam. Its working fine. Thanks a lot.
@johnborg54194 жыл бұрын
Thanks Mynda. That was interesting.
@MyOnlineTrainingHub4 жыл бұрын
Glad you enjoyed it, John!
@mohammedzubair3584 Жыл бұрын
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
@brenomachado28453 жыл бұрын
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.
@MyOnlineTrainingHub3 жыл бұрын
Nice alternative 👍
@zahiduppal32833 жыл бұрын
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
@MyOnlineTrainingHub3 жыл бұрын
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
@ldred26044 жыл бұрын
Brilliant, as always
@MyOnlineTrainingHub4 жыл бұрын
Thanks so much :-)
@shoaibrehman99884 жыл бұрын
Very useful thanks for sharing
@MyOnlineTrainingHub4 жыл бұрын
My pleasure, Ali! 😊
@InsightfulJourney123 Жыл бұрын
Thank you!
@MyOnlineTrainingHub Жыл бұрын
You're welcome!
@Kay-mk6uv2 жыл бұрын
This helped! Thank you!
@MyOnlineTrainingHub2 жыл бұрын
Great to hear!
@Kay-mk6uv2 жыл бұрын
Thank you for your videos, I hope your channel grows! It’s really really helpful!
@MyOnlineTrainingHub2 жыл бұрын
Thanks so much! Please spread the word about our channel.
@mariaf90563 жыл бұрын
Thanks for this. Now subscribed to your tutorials. How do I get the fiscal year to appear as 2018/19 instead of just 2018?
@MyOnlineTrainingHub3 жыл бұрын
Where J2 contains your date: =IF(MONTH(J2)
@mariaf90563 жыл бұрын
@@MyOnlineTrainingHub Thank you so much!
@mdandekar4 жыл бұрын
Thanks Mynda for the video. Any idea how the same can be done in case of 'Timelines' for a Pivot table/chart...?
@MyOnlineTrainingHub4 жыл бұрын
Hi Mandar, Timelines create their own groupings, you can't specify them based on fiscal periods, sorry.
@mdandekar4 жыл бұрын
@@MyOnlineTrainingHub Appreciate. Thnx :-)
@JonInSydney2 ай бұрын
Thanks for the formulae. It's solved something that's driving me silly ( I tried the formula suggested by CoPilot and Others =YEAR(A2) + (MONTH(A2) >= 7) but for some unknown reason 1 Jul2023 keeps showing up as July 2025!!!!) Can you also suggest a reason why I cant append Q as you suggested?
@MyOnlineTrainingHub2 ай бұрын
Glad it was helpful! I'm not able to replicate the issue with the Copilot formula. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@katrinaward51683 жыл бұрын
Great explanation - can you do this with Week Numbers as well?
@MyOnlineTrainingHub3 жыл бұрын
You'd have to convert the week numbers to dates, then covert those dates to fiscal periods.
@laghmanee2 жыл бұрын
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?
@cocopop1114 жыл бұрын
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?
@MyOnlineTrainingHub4 жыл бұрын
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.
@chaiyya3454 жыл бұрын
Hi Mynda, thanx for sharing. May i ask, why divided by 3? =ROUNDUP(MONTH)/3,0) Thank-You very much
@chaiyya3454 жыл бұрын
aaah.. i got it! because a quarter has 3 months..how silly i couldnt figure those
@harisbista7393 жыл бұрын
Thanks for the tutorial. What if the fiscal year starts from 3rd of JAN 2021 and ends at 2nd JAN 2022?
@MyOnlineTrainingHub3 жыл бұрын
Then you should use the last example where you have a lookup table.
@bzflowerbee4 жыл бұрын
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!
@MyOnlineTrainingHub4 жыл бұрын
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.
@thevloglife1054 жыл бұрын
You are really genius how to use some new function.
@MyOnlineTrainingHub4 жыл бұрын
Thanks so much, Nitesh :-)
@arvindramachandran99263 жыл бұрын
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.
@MyOnlineTrainingHub3 жыл бұрын
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.
@naimapedrono96654 жыл бұрын
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?
@MyOnlineTrainingHub4 жыл бұрын
Hi Naima, you can use the DATEVALUE function to convert the text to dates. =DATEVALUE(cell containing text date)
@naimapedrono96654 жыл бұрын
@@MyOnlineTrainingHub thank you so much Mynda for your time. It worked.
@lanaschludi54663 жыл бұрын
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!
@MyOnlineTrainingHub3 жыл бұрын
Hi Lana, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@DougHExcel4 жыл бұрын
Great video, now if we can just have orgs all use the same fiscal periods :-)
@MyOnlineTrainingHub4 жыл бұрын
:-D yes!
@alirazabokhari12453 жыл бұрын
great job! :)
@MyOnlineTrainingHub3 жыл бұрын
Thanks, Ali! 😊
@harshakumarky96974 жыл бұрын
One more Question, I have tried roundup function, but unable to get Q4, formula tried =Roundup (Month(@date)/4,0)
@MyOnlineTrainingHub4 жыл бұрын
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.
@skuzy2 жыл бұрын
is it possible to have this show WEEK based on fiscal year. eg 5 July = week 1 ?
@MyOnlineTrainingHub2 жыл бұрын
You can use the WEEKNUM function to return the week number for a date.
@skuzy2 жыл бұрын
@@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?
@harshakumarky96974 жыл бұрын
Hi, I have some doubts, if I have 4 qtr and my qtr starts from Apr- as Qtr1, then how will be the formula.
@MyOnlineTrainingHub4 жыл бұрын
Replace the 7 with 4 in the formula.
@harshakumarky96974 жыл бұрын
Thanks alot mynda,for your timely response....
@sushilsharma-mo7nb12 күн бұрын
how can we lock value in xlookup & vlookup if values are in table format
@MyOnlineTrainingHub11 күн бұрын
Not sure what you mean. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@mattmatt2453 жыл бұрын
What exactly prevents vlookup from looking for the date in a wrong column (qtr end) ?
@MyOnlineTrainingHub3 жыл бұрын
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: kzbin.info/www/bejne/al6YXod4nbSDbLM
@DebayanKar72 жыл бұрын
if I have a column values like FY22Q1 and want to convert into mm/dd/yyyy format ?
@MyOnlineTrainingHub2 жыл бұрын
How will you know what month it relates to seeing a quarter is 3 months.
@DebayanKar72 жыл бұрын
@@MyOnlineTrainingHub lets assume it will be the first day of the first month
@MyOnlineTrainingHub2 жыл бұрын
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.
@mohammadafsari46862 жыл бұрын
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 🌹🌹
@MyOnlineTrainingHub2 жыл бұрын
Hi Mohammad, Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@mohammadafsari46862 жыл бұрын
@@MyOnlineTrainingHub , thank you i send the file to forum 🙏🙏🙏
@henryg57354 жыл бұрын
And for us in the UK - tax year from 6 April to 5 April? Like the video!
@MyOnlineTrainingHub4 жыл бұрын
You need to use the same technique as the 4-5-4 calendar where you specify the date ranges for each quarter.
@LearnYouAndMe4 жыл бұрын
'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)
@MyOnlineTrainingHub4 жыл бұрын
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.
@LearnYouAndMe4 жыл бұрын
@@MyOnlineTrainingHub You are right but UDF is much user-friendly and clean if there is no inbuilt Excel function for the required output.
@jennifergray67952 жыл бұрын
I tried this and it’s not working for me. Did you have to define the date to @Date?
@MyOnlineTrainingHub2 жыл бұрын
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
@dmoney87ification3 жыл бұрын
I Can not get this to work it is returning the wrong year or says missing closing parenthesis..... Help!!!!!
@MyOnlineTrainingHub3 жыл бұрын
Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@dmoney87ification3 жыл бұрын
@@MyOnlineTrainingHub I have added it
@david70010 Жыл бұрын
Please I want a course for excel and one tshirt
@MyOnlineTrainingHub Жыл бұрын
😁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.
@tolulopejayeoba24484 жыл бұрын
still not getting this roundup function and i dont know why?
@MyOnlineTrainingHub4 жыл бұрын
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
@tolulopejayeoba24484 жыл бұрын
@@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
@gamexgamezone2 жыл бұрын
CHOOSE(YOU=TIME SAVER,LIFE SAVER) ... sorry ;P XD
@MyOnlineTrainingHub2 жыл бұрын
🙏😁
@melaniemarkwell868922 күн бұрын
First formula doesn’t work
@MyOnlineTrainingHub21 күн бұрын
If you're stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum