SpreadEveryone: The Fetchland Excel wire

1 lurker | 101 watchers
John66
John66
Aug 2011
4:55pm, 30 Aug 2011
20,401 posts
  •  
  • 0
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
Stander
Aug 2011
5:00pm, 30 Aug 2011
4,635 posts
  •  
  • 0
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
Stander
Aug 2011
5:01pm, 30 Aug 2011
4,636 posts
  •  
  • 0
Stander
My consultancy rates are very cheap btw.

:)
John66
John66
Aug 2011
5:08pm, 30 Aug 2011
20,403 posts
  •  
  • 0
John66
I know all that - sadly doesn't help one jot :-(
Stander
Stander
Aug 2011
5:10pm, 30 Aug 2011
4,639 posts
  •  
  • 0
Stander
Does the answer 365 in every year help? *

*Leap years = 366
HappyG(rrr)
HappyG(rrr)
Aug 2011
5:12pm, 30 Aug 2011
16,011 posts
  •  
  • 0
HappyG(rrr)
John, what's your question?

B1 - A1 = 1095 on my spreadsheet.

What are "relevant grant days"? :-)G
Stander
Stander
Aug 2011
5:14pm, 30 Aug 2011
4,640 posts
  •  
  • 0
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
GlennR
Aug 2011
5:15pm, 30 Aug 2011
2,951 posts
  •  
  • 0
GlennR
Happy has hit the nail on the head John - we need to know what the question is.
Stander
Stander
Aug 2011
5:15pm, 30 Aug 2011
4,641 posts
  •  
  • 0
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
Stander
Aug 2011
5:18pm, 30 Aug 2011
4,642 posts
  •  
  • 0
Stander
it did it again!

I'll fmail it to you John

About This Thread

Maintained by John66
This is bugging me, I'm sure it should be much easier

A1 has 25/6/2010
B1 has 24/6/2013

So t...

Related Threads

  • excel
  • support
  • tech
  • work








Back To Top

Tag A User

To tag a user, start typing their name here:
X

Free training & racing tools for runners, cyclists, swimmers & walkers.

Fetcheveryone lets you analyse your training, find races, plot routes, chat in our forum, get advice, play games - and more! Nothing is behind a paywall, and it'll stay that way thanks to our awesome community!
Get Started
Click here to join 113,878 Fetchies!
Already a Fetchie? Sign in here