If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Complicated Date Formula
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Complicated Date Formula

#1
July 6th 12, 07:41 AM
 Iain69 Junior Member First recorded activity by ExcelBanter: Jul 2012 Posts: 4
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
#2
July 6th 12, 07:43 AM
 Iain69 Junior Member First recorded activity by ExcelBanter: Jul 2012 Posts: 4

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
July 6th 12, 07:44 AM
 Iain69 Junior Member First recorded activity by ExcelBanter: Jul 2012 Posts: 4

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
July 6th 12, 07:45 AM
 Iain69 Junior Member First recorded activity by ExcelBanter: Jul 2012 Posts: 4

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

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Complicated formula? Leigh Excel Discussion (Misc queries) 4 November 22nd 07 01:37 AM Complicated formula Lemony_M Excel Discussion (Misc queries) 10 October 1st 07 03:59 PM Complicated Date formula Scady Excel Worksheet Functions 6 January 26th 07 05:28 AM Help on a complicated Formula santaviga Excel Programming 0 November 3rd 06 10:57 AM complicated if then formula...need help LincAg Excel Discussion (Misc queries) 3 May 25th 06 06:19 PM

All times are GMT +1. The time now is 06:56 PM.