+ Reply to Thread
Results 1 to 6 of 6

Help with condition format or formula to identify a leap year date in a cell

  1. #1
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Help with condition format or formula to identify a leap year date in a cell

    I have attached a sample sheet.
    I am doing a number of worksheets that will all take the start date from sheet 1. each sheet covers a 3 month period.

    starting at 31/1/12 the next date is 30/4/12 so it just adds 90 days and then similar for the next 3 month periods.
    The problem is that 2012 is a leap year so there are more days in February, so in 2013 the same calculation takes the next period to 1/5/2013 and then has a knock on effect for the following periods.
    The work book will cover a few years so I could do with a conditional format or a formula if possible to identify a leap year from the initial start date. It will probably be easier to refer to my sample sheet to get a better idea of what I'm after.
    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Help with condition format or formula to identify a leap year date in a cell

    Put this in D8:

    =IF(MOD(YEAR(C8),4)=0,"leap","")

    then copy down.

    Hope this helps.

    Pete

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Help with condition format or formula to identify a leap year date in a cell

    In D8 cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    No need to set the font color to white since the word leap will appear in D8 cell if the C8 cells is Leap Year. Otherwise the D8 will be Nothing.

    Hope that helps!


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Help with condition format or formula to identify a leap year date in a cell

    hi nje. you can get the results in Column H by using EOMONTH:
    =EOMONTH(C7,3)

    but if you need that text to show, try:
    =IF(MOD(YEAR(C7),4)<>0,"","leap")

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: Help with condition format or formula to identify a leap year date in a cell

    What a good response from you all, thank you very much. My problem is definitely solved.
    Pete, this is not the first time you have helped me out and I'm very grateful but I'm not allowed to 'star' you again apparently.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Help with condition format or formula to identify a leap year date in a cell

    Well, it's nice to get the stars, but it's not what I come on here for, so don't worry about it.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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