|
Aug 2011
6:11pm, 30 Aug 2011
3,278 posts
|
JamieKai *chameleon*
Not got time to look at this now, but after running will have a look and see if I can magic something - I spend half my life on Excel in a non-work role 
|
|
Aug 2011
7:44pm, 30 Aug 2011
4,647 posts
|
Stander
Have got the forumal to work everything out -almost, just working on the syntax for when the grant end date is before the year start date.
Brain now starting to ache.
|
|
Aug 2011
7:50pm, 30 Aug 2011
4,648 posts
|
Stander
GOT IT!!!!!!!!!!!!!!
|
|
Aug 2011
7:50pm, 30 Aug 2011
4,649 posts
|
Stander
=IF(enddate< =C1,0,IF(startdate< =C1,IF(enddate>D1,365,+enddate-C1),IF(startdate< =D1,IF(enddate>D1,+D1-startdate,"no"),0)))
|
|
Aug 2011
7:51pm, 30 Aug 2011
4,650 posts
|
Stander
Simple when you think about it.
John - I need a fag and cuppa and I'll tidy up the spreadsheet and e-mail it back to you.
|
|
Aug 2011
9:07pm, 30 Aug 2011
4,651 posts
|
Stander
*NERD MODE ON*
Slight correction. Should be:-
=IF($B3< =D$1,0,IF($A3< =D$1,IF($B3>E$1,365,+$B3-D$1),IF($A3< =E$1,IF($B3>E$1,+E$1-$A3+1,+$B3-$A3+1),0)))
*NERD MODE OFF*
Have e-mailed you the spreadsheet John (or will do it 10 seconds or so)
|
|
Aug 2011
9:23pm, 30 Aug 2011
10,308 posts
|
MissChappo
DNUQ
(is what I write in my notes when interviewing people who clearly Do Not Understand the Question, in this case applies to me)
|
|
Aug 2011
10:10pm, 30 Aug 2011
4,653 posts
|
Stander
Okay, for those really interested (God you must be bored):-
A3 = Start Date
B3 = end date
D1 = 31/12/09
E1 = 31/12/10
I dare any of you to now be able to explain the above forumla narratively.
And just to show off the $ signs in the forumla means it can be copied from row to row and column to column and it will autiomatically adjust itself correctly.
I really ought to get out a lot more.
|
|
Aug 2011
10:13pm, 30 Aug 2011
19,318 posts
|
JenL
I've been reading this thread since it started and I still don't understand it, though I do know that Excel is a spread sheet and not a brand of floor cleaner, which is what it makes me think of.
|
|
Aug 2011
10:14pm, 30 Aug 2011
4,654 posts
|
Stander
I started talking this problem through with my missus, but first had to explain that Excel, wasn't a shirt size (XL) or a large conference hall in the Docklands!
|
