# Dates and Leap Years

1. ## 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

2. ## Re: Dates and Leap Years

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

Cheers,

3. ## 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. ## 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.``

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

Cheers,

5. ## Re: Dates and Leap Years

this will get you 366 days

=(DATE(YEAR(B3)+1,MONTH(B3),DAY(B3))-B3)

6. ## 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. ## 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. ## 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. ## Re: Dates and Leap Years

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

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

#### Posting Permissions

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