Convert Dates to Fiscal Periods in Excel - Easy Formula - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
bzflowerbee
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!
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!
reply
Sheila
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.
reply
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.
reply
snicho
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.
reply
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.
reply
Lana
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!
reply
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!
reply
Nitin
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.
reply
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.
reply
Naima
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?
reply
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?
reply
Zahid
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
reply
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
reply
Murat
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.
reply
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.
reply
kevbelz
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)
reply
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)
reply
Paul
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)
reply
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)
reply
Add a review, comment
Other channel videos
