Complicated Date Formula

I posted this on another site today but have not managed to find an answer, does anyone on here know or can help???



Hi All, found this site when searching for the answer to an excel problem I am facing.

We need to track the number of days per calendar year an expat stays in this location and am trying to build a spreadsheet to track these days accurately from just entering arrival and leaving dates per employee.

The difficulty is twofold:
1 - Where the period spans a Year End ie. Arrives 31 Oct 2011 and Leaves 15 Feb 2012...how to get the formulae to count the days in that range relative to each year (see below screenshots)

2 - There always will be a cell not yet completed if the employee is still here so the days column uses the today() function to calculate but how to include this into my formulae for totalling per year, I think it is causing a problem also since there is no date in cell C8 on first screenshot.

Any ideas anyone? Am hoping someone can quickly drop on that Eureka formula that will solve all my problems.....well my excel ones anyway

Thanks,
Iain


****, I cannot seem to post the screenshots, let me see if I can type below (hope this comes out)

Example Form:

Employee Name Arrived Left Stayed
Mr ABC 1-May-11 15-Jul-11 75
Mr ABC 4-Aug-11 3-Oct-11 60
Mr ABC 6-Oct-11 13-Feb-12 130
Mr ABC 16-Feb-12 142
Mr ABC -
Mr ABC -
Mr ABC -
Mr ABC -
Mr ABC -
Mr ABC -
Mr ABC -


Formulae to calculate days (I know this is working wrong though
as noted in scenario 1)


1-Jan-07 1-Jan-08 1-Jan-09 1-Jan-10 1-Jan-11 1-Jan-12
31-Dec-07 31-Dec-08 31-Dec-09 31-Dec-10 31-Dec-11 31-Dec-12

2007 2008 2009 2010 2011 2012

Days per year 117 0



Actual Formula:

=SUMIFS($D$5:$D$119,$C$5:$C$119,"<="&K$3,$B$5:$B$1 19,">="&K$2)

where D5119 = No of Days column
B5:B119 = Arrival Dates and K3 is 31st Dec each year
C5:C119 = Arrival Dates and K3 is 31st Dec each year


Any ideas? Am totally stuck

THIS WAS A CLOSE REPLY

Maybe something like this

Assuming K4 = 2007; L4=2008; M4=2009; N4=2010; O4=2011;P4=2012

Array-formula in K5
=SUM(IF(YEAR($B$5:$B$200)=K$4,IF($C$5:$C$200="",TO DAY(),IF(YEAR($C$5:$C$200)>K$4,DATE(K$4,12,31),$C$ 5:$C$200))-$B$5:$B$200))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy across till P5

M.

BUT doesnt work if the 1st date is in a different year, ie the arrived say in Nov & left in Feb???



oops

The formula above has a flaw. It doesn't work if someone arrived on a previous year and still has not left.

M.

Anyone here able to crack this? Pleeeeeease......