# Date and Year Formatting

1. ## Date and Year Formatting

Hi Guys,

Hoping someone could help i need to create a formula for a date range.

2) In the navy blue area to the right (starting in cell M11), create a formula where each calender year is individually numbered.
* i.e; all of the first year will be Year 1, the following year is Year 2 etc.

Essenttiyy there are 5 years worth or data. So i need a formula that would say above the date e.g 01/02/19 would be YEAR 1, 01/03/20 Would be year 2 and so on.

thanks for helping
Excel.PNG

2. ## Re: Date and Year Formatting

Hi, welcome to the forum

Hard to see exactly what is in those cells, but this would be the basis of your formula...
=YEAR(TODAY())-2018

3. ## Re: Date and Year Formatting

Hi Ford,

I have uploaded the excel would you be able to take a look.

Many thanks

4. ## Re: Date and Year Formatting

Thanks for the file

This looks like a homework assignment?

5. ## Re: Date and Year Formatting

Starting form M11 copy to the right

="Year "&YEAR(M13)-2018

hahaha yess

7. ## Re: Date and Year Formatting

Thought so.

Please take another look at out rules and guidelines here
https://www.excelforum.com/forum-rul...rum-rules.html
Specifically guideline 3.

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

8. ## Re: Date and Year Formatting

can you give me guidence please

9. ## Re: Date and Year Formatting

sure, what have you tried so far?
Did you try to apply the suggestion I gave in post #2?

10. ## Re: Date and Year Formatting

Hi Ford,

Yes i used it and it returns the correct year e.g. 1, 2,3 etc

However i am confused on how to get the cell to say YEAR 1. It currently just says 1

Many thnaks once again

11. ## Re: Date and Year Formatting

You need to read up about how to concatenate different items (e.g. text and formulae) together into one cell. Google is your friend - let us know what you come up with.

12. ## Re: Date and Year Formatting

Thanyou ford and ali

I got it !

="Year "&YEAR(M13)-2018

14. ## Re: Date and Year Formatting

Awesome and well done!! Doesnt it feel great hat you figured this out on your own, rather than just being given the answer, which, incidentally, is what bebo suggested in post #5 (I had initially hidden his reply)?

There are currently 1 users browsing this thread. (0 members and 1 guests)