calculate number of days and splitting the it based on the year of the start and end date

**t2dela** · 05-08-2013, 02:34 AM

I'm trying to calculate the number of days within a given period, but split it where one total is the number of days that falls anytime before 12/31/2012 and the other totals the number of days anytime after 01/01/2013. And also, if the period is less than or equal to 12/31/2012, then cell I19 will calculate the number of days that is less than 12/31/2012 and leave cell I22 blank, and vise versa. I'm thinking I might have to use an "if" and "< or >" formula, but I don't know how. I've attached the spreadsheet that I'm working on. I managed to use a formula that splits the days (cell I19, calculates the days <12/31/2012, and cell I22 calculates the days >01/01/2013) but when I insert a date that is all in 2012 or all in 2013 (ex: 5/1/2012-5/30/12, or 5/1/2013-5/31/2013), it doesn't calculate it correctly The yellow highlighted cells are the ones I'm trying to figure out. Please help.

**t2dela** · 05-09-2013, 02:15 AM

I'm not sure which formula i should use. I'm trying to say that, if c8 and/or c10 is less than december 31, 2012, then calculate the number of days, and if c10 overlaps in years, then I want it to split the days where I19 will hold the total number of days for anything less than December 31, 2012 and I22 will calculate if cell c8 and c10 is greater than Jan 1, 2013, and if c10 overlaps in years, then just calculate in i22 the number of days that falls anytime after Jan 1, 2013. I'm willing to clarify anything if it's not too clear. Please help.

**Lemice** · 05-09-2013, 02:37 AM

Hello,

I have to say that your explanation was a bit confusing. Try this formula on I19

Please Login or Register  to view this content.

And this formula on I22

Please Login or Register  to view this content.

**t2dela** · 05-09-2013, 07:18 AM

Lem, thank you so much for responding. Your formula worked. Yay!!!

I apologize for the confusing description, i tried to be as detailed as possible without making it sound too confusing (failed) hehe. I'm just so glad you were able to figure it out. I've been struggling with this for days

**Lemice** · 05-09-2013, 07:24 AM

Glad that I could help.

If you have found a fitting solution to your problem, please mark the thread as [SOLVED] using the Thread Tools right above post #1. It keeps things neat and tidy.

And don't hesitate if you have any other question.

Have a great day.

**t2dela** · 05-09-2013, 11:29 AM

Sorry I'm back again.. I tried testing all possibilites and I noticed that when I entered 01/01/2013 - 12/31/2013 it came to 364 instead of 360.

**Lemice** · 05-09-2013, 04:48 PM

Not sure what you mean, but your formula on C12 is a bit weird.

I tried 01/01/2013 in the Start date. With 01/31/2013 in the End Date, both formula returns 30 (which is true, from 31st to 1st of January is 30 days).

However when I typed in 02/01/2013 in the End date, your formula still returns 30, while it should have been 31, shouldn't it?

For a year with 28 days in February, that year has 365 days, so the number of days between the first day (01/01/2013) and the last day (12/31/2013) counting the first day, there should be 364 days, no?

**t2dela** · 05-10-2013, 05:05 AM

Lem, you are correct, I forgot that i used a different formula for c12. what formula should I use instead? Also, if I need to include the first day in my equation, do I just add +1 to the formula?

=IF(C8<DATE(2013,1,1),IF(C10<DATE(2013,1,1),C10-C8,DATE(2012,12,31)-C8+1),0)

=IF(C8<DATE(2013,1,1),IF(C10<DATE(2013,1,1),0,C10-DATE(2013,1,1)+1),C10-C8)

**Lemice** · 05-10-2013, 05:37 AM

I'm pretty sure they are already included the first day. If you mean that you want to also count the last day, just add +1 to whatever part that is not 0.

=IF(C8<DATE(2013,1,1),IF(C10<DATE(2013,1,1),C10-C8+1,DATE(2012,12,31)-C8+1),0)

=IF(C8<DATE(2013,1,1),IF(C10<DATE(2013,1,1),0,C10-DATE(2013,1,1)+1),C10-C8+1)

calculate number of days and splitting the it based on the year of the start and end date

LinkBack

Thread Tools

Rate This Thread

Display

calculate number of days and splitting the it based on the year of the start and end date

Re: calculate number of days and splitting the it based on the year of the start and end d

Re: calculate number of days and splitting the it based on the year of the start and end d

Re: calculate number of days and splitting the it based on the year of the start and end d

Re: calculate number of days and splitting the it based on the year of the start and end d

Re: calculate number of days and splitting the it based on the year of the start and end d

Re: calculate number of days and splitting the it based on the year of the start and end d

Re: calculate number of days and splitting the it based on the year of the start and end d

Re: calculate number of days and splitting the it based on the year of the start and end d

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions