Trying to display yearly quarter using start date

[noize123]

Hi People.

I have a spreadsheet where i have a lot of start dates for projects and i am hoping to find a way to generate which quarter of next financial year it starts in.

I have attached a sheet to try and demonstrate what i mean.

So i have all the starts in column B starting from Cell B5, then in Column D starting from Cell D5 it lists what quarter of the next financial year the date lands from column B.

I was hoping to try and find some formula that could do this for me?

Si would expect in the two cells that mare blank, D15 would say Q4, and D16 would say Q1.

Hope i have explained this properly.

I was thinking of some type of formula that looked at two cells which had 01/04/2025 and 31/03/2025 and return Q1 if it was between these dates, then use + and repeat formula for the other quarters?

[AliGW]

How is October in Q1?

In D5 copied down:

="Q"&LOOKUP(MONTH(B5),{1,4,7,10},{4,1,2,3})

[noize123]

This works perfectly, thank you! have a great weekend

[AliGW]

Glad to have helped. You, too.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[alansidman]

An alternative using Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

[Czeslaw]

Power Query

Please Login or Register  to view this content.