First day of the week started from last year

Hi,

I have a situation in here. Our first day of 2019 started on 12/31/2018. I had data for the whole year of 2019 with the start date as 12/31/2018. So, when I group the date on pivot table, Excel group the 12/31/2018 as 4th quarter. Is there anything I can do to make excel recognize this is all 2019 data and our start day started on 12/31/2018?

Thanks for your help.

I would add a column to my raw data with a header like "Fiscal Year" with a formula like this, where A1 is the data for that row

Formula:

Please Login or Register  to view this content.

Then in your pivot table add the field Fiscal Year and filter on that. Do not use the built-in grouping. Do you need it by quarter, or just by year? If by quarter, how do you define your quarters?

There might be fancier way but that's the first thing that occurred to me.

I'd be curious about what you are doing. At my company our timesheet periods are by week, but all of our accounting is based on the calendar year. They have to do a little footwork for that one week of the year where the week is split between two fiscal years, but everything else becomes a lot easier.

Hello,

Thank you for your reply. I will test out the formula you provided and let you know if it works. I do remember I have seen a youtube tutorial on this subject when the first day of the year overlapped with last year and there is a calculation of it. However, I didn't save that video. :-(

Well, I don't know our company calendar but I do know that we use weekly to keep track the shipping and receiving data. So, when first week of the year of 2019 started 12/31/2018, that's all I know. I mean, I can just use the calendar year to do the quarterly analysis; however, that doesn't seem right to me. Do you understand what I mean?

Originally Posted by 6StringJazzer

I would add a column to my raw data with a header like "Fiscal Year" with a formula like this, where A1 is the data for that row

Formula:

Please Login or Register  to view this content.

Then in your pivot table add the field Fiscal Year and filter on that. Do not use the built-in grouping. Do you need it by quarter, or just by year? If by quarter, how do you define your quarters?

There might be fancier way but that's the first thing that occurred to me.

I'd be curious about what you are doing. At my company our timesheet periods are by week, but all of our accounting is based on the calendar year. They have to do a little footwork for that one week of the year where the week is split between two fiscal years, but everything else becomes a lot easier.

Hi There,
I've just tested the formula and it return the year; however, it won't help me group as quarter. I've attached two photos so you can see what I mean.

A screen shot is better than a photo, and attaching the actual file is better than a screen shot. Please see yellow banner at the top of the page.

I understand but this is from my company and it's restricted. That's why I use the photo. Thanks.