Help With Excel Formula Please

[Register1]

Hi wizards,

Please I need help on a formula.

I have in column 1 a date from 24 June 2017 up until 23 June 2020

I have made another column and named it YEAR

So the created formula should look at the dates in column 1, and if the date range is between 24 June 2017 and 31 December 2017, then in the same row, in my YEAR column, I would like the text to say Year 1

Then if the date range is between 01 January 2018 and 31 December 2018, then in the same row, in my YEAR column, I would like the text to say Year 2

Then if the date range is between 01 January 2019 and 31 December 2020, then in the same row, in my YEAR column, I would like the text to say Year 3

I just can't think how to do it.

R1

[AW111]

If the excel recognises the first column as dates, try
=YEAR(date column) - 2016

[Troubleatmill]

Just make sure column B is formatted as a number.

You might get the result 01/01/1900

[n3il123]

If you want it to actually say "Year 1" etc then you need a couple of ifs something like

=IF(YEAR(A60)=2018,"Year 1",IF(YEAR(A60)=2019,"Year 2",IF(YEAR(A60)=2020,"Year 3","Not Sure What Year")))

[Troubleatmill]

n3il123 said:

If you want it to actually say "Year 1" etc then you need a couple of ifs something like

=IF(YEAR(A60)=2018,"Year 1",IF(YEAR(A60)=2019,"Year 2",IF(YEAR(A60)=2020,"Year 3","Not Sure What Year")))

Or even simpler

Cell A2 contains the year.

Type this into cell B2

="Year "& (YEAR(A2) - 2016)

[Mr Pointy]

n3il123 said:

If you want it to actually say "Year 1" etc then you need a couple of ifs something like

=IF(YEAR(A60)=2018,"Year 1",IF(YEAR(A60)=2019,"Year 2",IF(YEAR(A60)=2020,"Year 3","Not Sure What Year")))

Have you seen the new IFS function?
https://www.youtube.com/results?search_query=excel...

[Register1]

Hi All,

Many thanks,

I think I wasn't quite clear enough. My bad, sorry.

Column A has for example 500 random dates from 24 June 2017 up until 23 June 2020 in 500 rows.
I want to place a formula into column B next to the 500 dates.

I was considering the formula would occupy column B and the output could be column B,



So if the date in column A is between 24 June 2017 and 23 June 2018, then the formula output generated into column B should say Year 1

If the date in column A is between 24 June 2018 and 23 June 2019, then the formula output generated into column B should say Year 2

If the date date in column A is between 24 June 2019 and 23 June 2020, then the formula output generated into column B should say Year 3

Please help.

R1

[Mr Pointy]

That's a toally different problem. In the first one you wanted each calender year identified (2107, 2018, 2019 etc) which is easy. The second one has custom periods.

[Register1]

Troubleatmill said:

Or even simpler

Cell A2 contains the year.

Type this into cell B2

="Year "& (YEAR(A2) - 2016)

ALMOST

But not from 01 January to 31 December
More like 24 June 2017 to 23 June 2018

Thanks

R1

[ATG]

Create a separate table with your boundary dates and the year 1, year2 etc strings

Then pick values from that new table using vlookup()

[Register1]

Mr Pointy said:

That's a toally different problem. In the first one you wanted each calender year identified (2107, 2018, 2019 etc) which is easy. The second one has custom periods.

Yeah, SORRY
I understood wrong.

My mistake.

R1

[Register1]

ATG said:

Create a separate table with your boundary dates and the year 1, year2 etc strings

Then pick values from that new table using vlookup()

Hi AGT,

Sorry, can you explain more simpler.
I get the vLookup bit, but how to create boundary tables ?

R1

[TallTom]

Why not just sort all the columns, find those cut off dates... fill in Year 1 at the end of year 1, then add a year 2, then find end of the range on year 2.... etc

If its a one off exercise, this will be quicker than creating a formula. should take under a minute to do the above. even with 100,000 rows etc

[Register1]

TallTom said:

Why not just sort all the columns, find those cut off dates... fill in Year 1 at the end of year 1, then add a year 2, then find end of the range on year 2.... etc

If its a one off exercise, this will be quicker than creating a formula. should take under a minute to do the above. even with 100,000 rows etc

Hi Tom,

I have 1 column, that is column A
Column A has 500 or more random dates from 24 June 2017 up until today, 23 June 2020

I was looking for something, a formula, to put into each cell in column B, that would give me a "Year 1" or "Year 2" or "Year 3"

Cheers

R1

[Mr Pointy]

If you only have 500 entries just do a SORT on column A (the date) & assign the Year 1 etc values manually. It would take a minute to do.

If you need to get back to the original order afterwards create an index column numbered 1-500 first so you can sort again on that.

[TallTom]

Register1 said:

Hi Tom,

I have 1 column, that is column A
Column A has 500 or more random dates from 24 June 2017 up until today, 23 June 2020

I was looking for something, a formula, to put into each cell in column B, that would give me a "Year 1" or "Year 2" or "Year 3"

Cheers

R1

What I am saying is you dont need a formula, especially as a once off.

Sort the Column A data, decending/Accending whatever you need.

Your cut off dates, search for the end of year 1 so if its 20th June 2018 for example, Put Year 1 in Column B for the last record, below that Put Year 2

Then search for Year 2 end date so 20th June 2019, Put "Year 2" in column B there and Year 3 in column B for the record below.

Go to column A last record (Press Control/ctrl and down key) and enter Year 3 in its column B, Copy the "Year 3"

Press Ctrl+shift+up arrow and then keep holding shift and press down key once, then paste "Year 3" repeat for year 1/2 records as you have your start/end dates and just filling in those gaps

no need to complicate with a formula.

[nyt]

=IFS(A1>DATE(2019,6,24),3,A1>DATE(2018,6,24),2,A1>DATE(2017,6,24),1)

[Doofus]

Nested Ifs.

=If(A1< 24/06/18,"Year 1",if(A1< 24/06/19,"Year 2","Year 3"))

You'll need to replace those dates with date numbers, but I don't know them off the top of my head.

[ATG]

Register1 said:

ATG said:

Create a separate table with your boundary dates and the year 1, year2 etc strings

Then pick values from that new table using vlookup()

Hi AGT,

Sorry, can you explain more simpler.
I get the vLookup bit, but how to create boundary tables ?

R1

Enter your year-end dates in the first column of a table and their Year N strings in the next column.

Let's say you've created that table in columns A and B.

Your list of 500 random dates is in column D

Your formula in column E is something like:
=Vlookup( D1, A1:B5, 2, TRUE)

I.e. you're looking up the values in col D in the table in cols AB, returning the value from col B and the "TRUE" means you're telling vlookup to use an approximate match, which really means "find the nearest match with a lower value"

[Mr Pointy]

nyt said:

=IFS(A1>DATE(2019,6,24),3,A1>DATE(2018,6,24),2,A1>DATE(2017,6,24),1)

Delicious