Complicated Date Range Formula?

[Iain69]

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:
[TABLE="width: 507"]
<TBODY>[TR]
[TD]Employee Name[/TD]
[TD]Arrived[/TD]
[TD]Left[/TD]
[TD]Stayed[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD]1-May-11[/TD]
[TD]15-Jul-11[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD]4-Aug-11[/TD]
[TD]3-Oct-11[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD]6-Oct-11[/TD]
[TD]13-Feb-12[/TD]
[TD]130[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD]16-Feb-12[/TD]
[TD][/TD]
[TD]142[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]Mr ABC[/TD]
[TD][/TD]
[TD][/TD]
[TD]-[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]


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

[TABLE="width: 553"]
<TBODY>[TR]
[TD][/TD]
[TD]1-Jan-07[/TD]
[TD]1-Jan-08[/TD]
[TD]1-Jan-09[/TD]
[TD]1-Jan-10[/TD]
[TD]1-Jan-11[/TD]
[TD]1-Jan-12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]31-Dec-07[/TD]
[TD]31-Dec-08[/TD]
[TD]31-Dec-09[/TD]
[TD]31-Dec-10[/TD]
[TD]31-Dec-11[/TD]
[TD]31-Dec-12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2007[/TD]
[TD]2008[/TD]
[TD]2009[/TD]
[TD]2010[/TD]
[TD]2011[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Days per year[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]117[/TD]
[TD]0[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=2><COL><COL><COL></COLGROUP>[/TABLE]



Actual Formula:
[TABLE="width: 459"]
<TBODY>[TR]
[TD]=SUMIFS($D$5:$D$119,$C$5:$C$119,"<="&K$3,$B$5:$B$119,">="&K$2)[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

where D5:D119 = 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

 

[Marcelo Branco]

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="",TODAY(),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.

 

[Marcelo Branco]

oops

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

M.

 

[Iain69]

Wow that's complicated and I tried it. It does work for year 2011 but not for 2012 as it does not seem to count the days from 1st Jan to 13th Feb but only from 16th Feb.

I think this is because it only counts if the Arrival date is in the same year so would need to amend that somehow. Getting close though but need to somehow get the earlier dates in? Any idea? Apologies if this one took a while but it is certainly very much appreciated

Iain

 

[Iain69]

Ah I see you beat me to the reply, sorry I hadnt refreshed my screen, I will try to see if I can include that somehow...
Iain

 

[madforgolf]

Hi
This works across different years. Column D gives days including the start date. Came across Column F formulas in a search ages ago but forget who wrore it.

Excel Workbook
	B	C	D	E	F
7	01/05/2011	15/07/2012	442		63 Weeks 1 Days
8	01/05/2012	16/07/2012	77		11 Weeks 0 Days
Sheet1

 

[Iain69]

That last one though does not sum the total days in a specific year, is simply number of dates in a range? Marcelo had it but for the opening date not being in the same year?

Have been tearing my hair out all morning on this?

I could probably add in hidden columns for each year and do an if formulae along the lines you mention above then total down the way but am trying not to do that and just incorporate it into 1 formula as I will have many tabs for different people and want to copy that cell accross once done

A rework of Marcelo's would be best if it is possible but it is above my brain power unfortunately ;(

 

[Iain69]

Any more thoughts anyone? Any other users know what I can do?

Thanks,
Iain

 

[Marcelo Branco]

Any more thoughts anyone? Any other users know what I can do?

Thanks,
Iain

New version - ugly array formula (maybe someone has a better solution, but i think this works)

Assuming 2007 to 2012 in K4:P4

K5
=SUM(IF($B$5:$B$200<>"",IF(YEAR($B$5:$B$200)=K$4,IF($C$5:$C$200="",IF(K$4=YEAR(TODAY()),TODAY(),DATE(K$4,12,31)),IF(YEAR($C$5:$C$200)>K$4,DATE(K$4,12,31),$C$5:$C$200))-$B$5:$B$200,IF(K$4>YEAR($B$5:$B$200),IF($C$5:$C$200="",IF(K$4=YEAR(TODAY()),TODAY()-DATE(K4,1,1),365),IF(K$4=YEAR($C$5:$C$200),$C$5:$C$200-DATE(K$4,1,1)))))))

Ctrl+Shift+Enter

copy across till P5

M.

 

[Iain69]

New version - ugly array formula (maybe someone has a better solution, but i think this works)

Assuming 2007 to 2012 in K4:P4

K5
=SUM(IF($B$5:$B$200<>"",IF(YEAR($B$5:$B$200)=K$4,IF($C$5:$C$200="",IF(K$4=YEAR(TODAY()),TODAY(),DATE(K$4,12,31)),IF(YEAR($C$5:$C$200)>K$4,DATE(K$4,12,31),$C$5:$C$200))-$B$5:$B$200,IF(K$4>YEAR($B$5:$B$200),IF($C$5:$C$200="",IF(K$4=YEAR(TODAY()),TODAY()-DATE(K4,1,1),365),IF(K$4=YEAR($C$5:$C$200),$C$5:$C$200-DATE(K$4,1,1)))))))

Ctrl+Shift+Enter

copy across till P5

M.

WOW!!!! You are a genius Marcelo. If it works I dont mind if no other answer. Am home now but will try first thing on Monday, this is great, thank you...