+ Reply to Thread
Results 1 to 9 of 9

Dates and Leap Years

  1. #1
    Registered User
    Join Date
    10-05-2011
    Location
    So Cal
    MS-Off Ver
    Excel 2003
    Posts
    10

    Dates and Leap Years

    Hi All,
    I'm new so be gentle. If I'm in the wrong area, my apologies.

    I have a spreadsheet that I am trying to calculate an inmates release date based on days sentenced. There are 2 fields, Field 1 "Years" Field 2 "Months". Staff enters the time sentenced for example: Sentenced 10/3/2011. Length 1 Year, 4 Months.

    Most of my rudimentry calculations are working but they are not calculating leap years. I know I need to include the sentenced date in here somewhere, but not sure where. I have attached my file. When you look at the Years to Days and Months to Days, it does not take into account 2012, a leap year.

    Any help is appreciated...

    Thanks
    JEA
    Attached Files Attached Files

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Dates and Leap Years

    How about using the DATE function instead:

    Please Login or Register  to view this content.
    (Cell refs based on your example).

    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Registered User
    Join Date
    10-05-2011
    Location
    So Cal
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Dates and Leap Years

    That was quick...Thank you. I should have been a little more clearer on what my problem is. In cell B6 and B8, since the sentence will run into and past 2012, those should show 366 and 123 respectively. I don't know if that is possible...

  4. #4
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Dates and Leap Years

    I'm not sure how meaningful those calculations will be. If you want to know the length of the sentence in days, then you may be better off using the DATE function I provided in your B15 cell, then use:

    Please Login or Register  to view this content.
    in your B10 cell.

    This would eliminate the errors you're getting in both B6 and B8.

    Cheers,

  5. #5
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Dates and Leap Years

    this will get you 366 days

    =(DATE(YEAR(B3)+1,MONTH(B3),DAY(B3))-B3)
    Windows 7 using Office 2007 & 2010

    Remember your [ code ] [ /code ] tags, makes reading soooo much easier

  6. #6
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Dates and Leap Years

    =B15-(DATE(YEAR(E26),MONTH(E26)-4,DAY(E26)))

    this will get you 123 month days, format this and my last post as general

  7. #7
    Registered User
    Join Date
    10-05-2011
    Location
    So Cal
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Dates and Leap Years

    Everything seems to be working as it should. I'm at home now on my Mac (sorry)...I can't seem to get the last post working. There is no cell E26 in the mix. Did I miss something?

  8. #8
    Valued Forum Contributor scottylad2's Avatar
    Join Date
    09-03-2010
    Location
    edinburgh
    MS-Off Ver
    Office 2007 Prof & Office 2010 Student Edition
    Posts
    629

    Re: Dates and Leap Years

    Appologies, thats a cell I was using for scribbles.....substitute E26 for B15 I had the release date in another cell for my cals....Should read now as this

    =B15-(DATE(YEAR(B15),MONTH(B15)-4,DAY(B15)))

  9. #9
    Registered User
    Join Date
    10-05-2011
    Location
    So Cal
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: Dates and Leap Years

    That did it...Thank you both so very much for your time, knowledge and patience...Semper Fi..

+ 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