time decomposition formula

[timtim91]

Hi,

I would like to produce a formula which would return a TEXT in attached file highlighted in yellow. There are other supportive columns you will see, green highlighted is changing depending on the number of the week.

The final value (yellow highlighted) would be dependent on the position day (column A). While I know how to deal with DAY and WEEK, I am struggling to cluster months in to QUARTER and YEAR.

Please if anyone could advise.

Thank you
Tim

[Glenn Kennedy]

I do not understand what you are trying to do. What is the raw data? What formulae (if any) are meant to be presnet in the other columns?


You day you have days and weeks fixed. How? Where are the formulae that did that for you?

Without some explanation, I can see neither what YOU have done nor what you WANT to get done.

[Root_]

See if the following formula works for you:

=IF(LEFT(E2)="D","DAY",IF(LEFT(E2)="W","WEEK",IF(LEFT(E2)="M",IF(YEAR(A2)<>YEAR(F2),"YEAR",IF(CEILING(MONTH(A2),3)<>CEILING(MONTH(F2),3),"QUARTER","MONTH")),"")))