for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

2 lurkers | 102 watchers

1
2
3
4
Next
Last

John66 Aug 2011 4:55pm, 30 Aug 2011 20,401 posts	Quote Pin John66 This is bugging me, I'm sure it should be much easier A1 has 25/6/2010 B1 has 24/6/2013 So the date range of my grant is from 25/6/10 to 24/6/2013 and the number of days is 1095. Easy so far. I want a formula to calculate, for a lot of grants, how many relevant grant days are in 2010, 2011, 2012, 2013, 2014 etc. Make sense??
Stander Aug 2011 5:00pm, 30 Aug 2011 4,635 posts	Quote Pin Stander Dates in excel are actually numbers counting up from 0th Jan 1900 (which is value zero) . If you have Cell A2 as +A1 but format it as a number you will see what I mean. If you type in the dates you what you can therefore do a simple deduction, but just format the answer as a number not a date. Clear?
Stander Aug 2011 5:01pm, 30 Aug 2011 4,636 posts	Quote Pin Stander My consultancy rates are very cheap btw.
John66 Aug 2011 5:08pm, 30 Aug 2011 20,403 posts	Quote Pin John66 I know all that - sadly doesn't help one jot
Stander Aug 2011 5:10pm, 30 Aug 2011 4,639 posts	Quote Pin Stander Does the answer 365 in every year help? * *Leap years = 366
HappyG(rrr) Aug 2011 5:12pm, 30 Aug 2011 16,011 posts	Quote Pin HappyG(rrr) John, what's your question? B1 - A1 = 1095 on my spreadsheet. What are "relevant grant days"? :-)G
Stander Aug 2011 5:14pm, 30 Aug 2011 4,640 posts	Quote Pin Stander =if formula? i.e. =if(A1<40179,0,40544-a1) 40179 = 01/01/10 40544 = 01/01/11 i.e. the forumla returns a value of zero unless your date falls within 2010 in which case it will then give you the number of days in 2010
GlennR Aug 2011 5:15pm, 30 Aug 2011 2,951 posts	Quote Pin GlennR Happy has hit the nail on the head John - we need to know what the question is.
Stander Aug 2011 5:15pm, 30 Aug 2011 4,641 posts	Quote Pin Stander Shit. What happened to trest of my post? =if(a1<40179,0,40544-a1) returns a value of zero for dates before 01/01/10 (value 40179), but if the date is within 2010 (1/1/10 = value 40544) it then gives you the number of days within 2010. Helpful?
Stander Aug 2011 5:18pm, 30 Aug 2011 4,642 posts	Quote Pin Stander it did it again! I'll fmail it to you John