find number of days ..

[GaryPanic]

Hey guys

bit of a weird one here
I have been asked (Excel) to find out how many days fall in a date range
simples ..
however here it is

start date X =01.11.12 to Y = 31.10.13
start date could be any date > 01.11.12 and end date could be any date up to 31.10.13

how many days are in the tax year
(a) 05.04/12
(b) 05.04.13

so
01.12.12 to 15.04.13 - should give 125 days in 12 yoa and 10 days in 13 yoa
01.05.13 to 05.09.15 should give 0 days in 12 yoa and 127 in 13 yoa


I then need to get a figure /365 * this number (that bits easy ) - to give the tax benefit per year ..
at the moment its in a spreadsheet - but if needs be - will migrate to a d/b

any pointers -

 

[Brianwarnock]

Quick question as I'm about to go out for the day.
Are you ignoring leap years?

Other than that unclear as to the problem as it appears to be simple arithmetic with a few tests for dates, shouldn't be too difficult in code.

Brian

 

[GaryPanic]

I was going to - but would be ok use
date year start to tax year end = number of days

 

[Brianwarnock]

Actually on rereading this is simpler than I thought, given the restricted date range, merely

5/4/13 - start date
end date - 5/4/13

but I'm sure you have done this now.

Brian

 

[GaryPanic]

Either I am dumb (probably) or I have missed something

the start date 01/10/12 and end date 31/09/13
how many days are their in each date range of the following

tax year x (05/04/11 to 04/04/12)
tax year x+1 (05/04/12 to 04/04/13)

its easy if its fixed but the start date could be
01/04/12 and end say 01/05/12
3 days in one year and 27 in another
they may not be full 12 month periods and some may start and finish in the same year

 

[GaryPanic]

How many days in this range how many days in this range
06/04/12 to 05/04/13 06/04/13 to 05/04/14
06/04/2012
05/04/2013
Initials Date of Birth Level of Cover Country of Domicile Start Date Leave Date Days on Cover Annual Premium Pro-Rata Premium
14.06..15 whatever uk 01 Oct 12 30 Sep 13 365 £132.00 £132.00
UK 01 Oct 12 30 Sep 13 365 £132.00 £132.00
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £132.00 £132.00
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £132.00 £132.00
UK 01 Oct 12 30 Sep 13 365 £97.75 £97.75
UK 01 Oct 12 30 Sep 13 365 £132.00 £132.00
UK 01 Oct 12 06 May 13 218 £71.40 £42.64
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £97.75 £97.75
UK 01 Oct 12 30 Sep 13 365 £71.40 £71.40
UK 01 Oct 12 30 Sep 13 365 £132.00 £132.00
UK 01 Oct 12 30 Sep 13 365 £132.00 £132.00
UK 01 Oct 12 30 Sep 13 365 £132.00 £132.00
UK 05 Jun 13 30 Sep 13 118 £71.40 £23.08

here is a sample

 

[GaryPanic]

just uploaded a xls of the data - (no info of course )
I may have to dump in to dbase - but if i can live in xls would be easier

 

[Brianwarnock]

Either I am dumb (probably) or I have missed something

the start date 01/10/12 and end date 31/09/13
how many days are their in each date range of the following

tax year x (05/04/11 to 04/04/12)
tax year x+1 (05/04/12 to 04/04/13)

its easy if its fixed but the start date could be
01/04/12 and end say 01/05/12
3 days in one year and 27 in another
they may not be full 12 month periods and some may start and finish in the same year

I still don't see the problem although there is an error in my formula, it should be
End date -(5/4/13-1) to allow for it to inclusive of the 5th

The only possible problem arises if it is possible for the range not to span the TX year change, but this can be tested for.

Or am I missing something ?

Brian

 

[Brianwarnock]

Hi
have completed your sample.
I made a small change as you will see.
Obviously if range spans more than one tax year more work will be needed.
Hope this is what you want.

Brian

 

[GaryPanic]

I am reviewing this (sorry for the delay in reverting)
I will let you know if it works in the real data ...

Big thanks in advance

G

 

[GaryPanic]

digesting still


but

"Obviously if range spans more than one tax year more work will be needed.
Hope this is what you want."


that's the issue - but I think what you have done - will point me in the right direction .

 

[GaryPanic]

Brian ...
your formula is short (compared to my long winded one
but it's not quite doing it

if you look at Row 56

your formula quotes 187 (column K )
but the actual number of days is 105 (column H)

Once this (K column) is working - the rest is easy as it will be straight forward k-h gives me x dates in the next year

 

[Brianwarnock]

they may not be full 12 month periods and some may start and finish in the same year

I did not appreciate that this meant the same tax year as none of your original examples did, and sorry I missed it in your sample sheet. I always desin test data to test all possibilities rather than rely on live data so have amended your first three rows of data to test the simple changes I have made.

I the data can span multiple tax years say 1/1/11 to 6/7/2014 then you will need to think if there are any limits, the worksheet IF will get quite complex and a UDF would be better.

Brian