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]

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

Hope it works. Worked for me but i had not time to fully testing.

M.

 

[straighttovideo]

A possible inelegant solution:

Iin a new sheet (you can hide it when done if it makes you feel better) enter the following in the first 5 columns of row 1: 2007, 2008, 2009, 2010, 2011, 2012
Select the range (A1:F1) and rename it "BlockHeaders"

Directly below it (in B1) enter the following:
=IF(AND(YEAR(DATE(A$1,1,0+ROWS(A$2:A2)))=A$1,DATE(A$1,1,0+ROWS(A$2:A2))<=TODAY()), DATE(A$1,1,0+ROWS(A$2:A2)),"")

Copy paste the formula into a range 5 Col x 366 Rows (the bottom right corner should be F367)

You'll now have a giant block of dates where each column has all the dates for the year directly above the block in the BlockHeaders. Dates that haven't happened yet will be nullstrings.

Rename this range (B2:F367) "BlockOfDates"


[TABLE="width: 426"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="align: right"]1-Jan-07[/TD]
[TD="align: right"]1-Jan-08[/TD]
[TD="align: right"]1-Jan-09[/TD]
[TD="align: right"]1-Jan-10[/TD]
[TD="align: right"]1-Jan-11[/TD]
[TD="align: right"]1-Jan-12[/TD]
[/TR]
[TR]
[TD="align: right"]2-Jan-07[/TD]
[TD="align: right"]2-Jan-08[/TD]
[TD="align: right"]2-Jan-09[/TD]
[TD="align: right"]2-Jan-10[/TD]
[TD="align: right"]2-Jan-11[/TD]
[TD="align: right"]2-Jan-12[/TD]
[/TR]
</tbody>[/TABLE]
...
[TABLE="width: 426"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD="align: right"]30-Dec-07[/TD]
[TD="align: right"]29-Dec-08[/TD]
[TD="align: right"]30-Dec-09[/TD]
[TD="align: right"]30-Dec-10[/TD]
[TD="align: right"]30-Dec-11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31-Dec-07[/TD]
[TD="align: right"]30-Dec-08[/TD]
[TD="align: right"]31-Dec-09[/TD]
[TD="align: right"]31-Dec-10[/TD]
[TD="align: right"]31-Dec-11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]31-Dec-08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On your first sheet, after days worked, add the following column headers: 2007, 2008, 2009, 2010, 2011, 2012

In the cell directly under 2007 enter the following:
=SUM(($B3<=INDEX(BlockOfDates,0,MATCH(E$2,BlockHeaders,0)))*(INDEX(BlockOfDates,0,MATCH(E$2,BlockHeaders,0))<=IF($C3="",TODAY(),PunchOut)))
and Ctr-Alt-Enter as it's an array formula.

$B3: Arrival Cell
$C3: Depart Cell
E$2: The cell containing the year to search by, in this case 2007.

Use the fill handle to copy it right till it's under all the dates. Then you can fill it down the rows and it should work just fine.

And there you go.

Should look something like this (Except that my sheet doesn't have your days column, but you get the idea):

[TABLE="width: 598"]
<colgroup><col span="2"><col span="7"></colgroup><tbody>[TR]
[TD]In[/TD]
[TD]Out[/TD]
[TD][/TD]
[TD="align: right"]2007[/TD]
[TD="align: right"]2008[/TD]
[TD="align: right"]2009[/TD]
[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/07[/TD]
[TD="align: right"]12/31/12[/TD]
[TD][/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]188[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/08[/TD]
[TD="align: right"]06/09/11[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]359[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]365[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]02/14/11[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]321[/TD]
[TD="align: right"]188[/TD]
[/TR]
[TR]
[TD="align: right"]06/05/09[/TD]
[TD="align: right"]09/30/10[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]09/13/10[/TD]
[TD="align: right"]05/18/10[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]



Lemme know how(/if) it works for you,

Best
Brian

 

[Marcelo Branco]

My formula in #9 is not working in all cases

Rethinking... (sorry)

M.

 

[Marcelo Branco]

My last shot

Insert this UDF in a standard module
Copy (Ctrl+C) the code below
Alt+F11 to open the VBEditor
go to Insert > Module
Paste (Ctrl+V) in the right panel

Function isLeapYear(ByVal YY As Long) As Boolean
    'Posted by Tusharm
    'http://www.mrexcel.com/forum/showthread.php?70215-Leap-Year-Test-VBA
    isLeapYear = IIf(YY Mod 100 = 0, YY Mod 400 = 0, YY Mod 4 = 0)
End Function

Back to Excel

Assuming 2007 to 2012 in K4:P4

Array formula in 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+1)))+SUM(IF($B$5:$B$200<>"",IF($B$5:$B$200<>YEAR($K4),IF(K$4>YEAR($B$5:$B$200),IF($C$5:$C$200="",IF(K$4=YEAR(TODAY()),TODAY()-DATE(K4,1,1)+1,IF(isLeapYear(K$4),366,365)),IF(K$4=YEAR($C$5:$C$200),$C$5:$C$200-DATE(K$4,1,1)+1,IF(K$4 < YEAR($C$5:$C$200),IF(isLeapYear(K$4),366,365))))))))<YEAR($C$5:$c$200),if(isleapyear(k$4),366,365))))))))


confirmed with Ctrl+Shift+Enter

copy across till P5

Save the file as macro enabled

M.</YEAR($C$5:$c$200),if(isleapyear(k$4),366,365))))))))

 

[Marcelo Branco]

oops..

Another mistake..(sorry)

Formula in 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+1)))+SUM(IF($B$5:$B$200<>"",IF(YEAR($B$5:$B$200)<>K$4,IF(K$4>YEAR($B$5:$B$200),IF($C$5:$C$200="",IF(K$4=YEAR(TODAY()),TODAY()-DATE(K4,1,1)+1,IF(isLeapYear(K$4),366,365)),IF(K$4=YEAR($C$5:$C$200),$C$5:$C$200-DATE(K$4,1,1)+1,IF(K$4 < YEAR($C$5:$C$200),IF(isLeapYear(K$4),366,365))))))))

Ctrl+Shift+Enter

M.

 

[Iain69]

Marcelo, thanks for this but afraid I have never used VB editor before. I have tried as you said but get an error saying invalid outside procedure??

What part should I copy? I just copied:

isLeapYear = IIf(YY Mod 100 = 0, YY Mod 400 = 0, YY Mod 4 = 0)
End Function

Should there be spaces? Do you know what I am doing wrong?

Regards,
Iain

 

[Marcelo Branco]

1. Copy this

Function isLeapYear(ByVal YY As Long) As Boolean
'Posted by Tusharm
'http://www.mrexcel.com/forum/showthread.php?70215-Leap-Year-Test-VBA
isLeapYear = IIf(YY Mod 100 = 0, YY Mod 400 = 0, YY Mod 4 = 0)
End Function

2. Alt+F11 to open the VBEditor

3.go to Insert > Module

4. Paste in the right panel


5.. Back to Excel

6.Copy this formula

=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+1)))+SUM(IF($B$5:$B$200<>"",IF(YEAR($B$5:$B$200)<>K$4,IF(K$4>YEAR($B$5:$B$200),IF($C$5:$C$200="",IF(K$4=YEAR(TODAY()),TODAY()-DATE(K4,1,1)+1,IF(isLeapYear(K$4),366,365)),IF(K$4=YEAR($C$5:$C$200),$C$5:$C$200-DATE(K$4,1,1)+1,IF(K$4 < YEAR($C$5:$C$200),IF(isLeapYear(K$4),366,365))))))))

7.Select K5

8. Paste the formula in the formula bar

9.with the cursor inside the formula bar
Ctrl+Shift+Enter simultaneously

10. copy (drag) across

In Excel 2007 or higher save the fie with the extension .xlsm

M.

 

[Iain69]

ok, thanks Marcelo. I dont want to start putting links to the internet into my spreadsheets that I dont entirely know what they do, was hoping it could just be addressed a by formula.

Will keep trying but thanks for your help.

Cheers,
Iain