Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 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 |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
Anyone here able to crack this? Pleeeeeease......
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated formula? | Excel Discussion (Misc queries) | |||
Complicated formula | Excel Discussion (Misc queries) | |||
Complicated Date formula | Excel Worksheet Functions | |||
Help on a complicated Formula | Excel Programming | |||
complicated if then formula...need help | Excel Discussion (Misc queries) |