+ Reply to Thread
Results 1 to 14 of 14

Date and Year Formatting

  1. #1
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    6

    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
    Attached Files Attached Files
    Last edited by Dan4488; 04-26-2019 at 11:33 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Date and Year Formatting

    Hi Ford,

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

    Many thanks

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Date and Year Formatting

    Thanks for the file

    This looks like a homework assignment?

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Date and Year Formatting

    Starting form M11 copy to the right

    ="Year "&YEAR(M13)-2018
    Quang PT

  6. #6
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Date and Year Formatting

    hahaha yess

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #8
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Date and Year Formatting

    can you give me guidence please

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #10
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    6

    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. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  12. #12
    Registered User
    Join Date
    04-26-2019
    Location
    Australia
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Date and Year Formatting

    Thanyou ford and ali

    I got it !

    ="Year "&YEAR(M13)-2018

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,412

    Re: Date and Year Formatting

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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)?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Date from Week and Year, Std formatting
    By torers in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-26-2015, 08:36 AM
  2. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  3. Formatting a date, excluding the year
    By kylecampbellcore in forum Excel General
    Replies: 4
    Last Post: 08-21-2013, 01:21 PM
  4. Replies: 5
    Last Post: 08-22-2012, 05:06 PM
  5. Replies: 9
    Last Post: 05-01-2012, 09:52 PM
  6. Conditional Formatting, Date Greater Than 1 Year
    By jshpik1 in forum Excel General
    Replies: 10
    Last Post: 11-24-2010, 07:32 PM
  7. Date Formula Question: date+1 year formatting
    By sheldons86 in forum Excel General
    Replies: 1
    Last Post: 07-30-2008, 04:41 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1