calcualte months worked this tax year.

**maverikk** · 05-29-2009, 06:54 PM

What I need to do is calculate the number of months worked by a staff member in this tax year if they enter their start date - taking into account that their start date may be in 2001 (or other) - I need to disregard all information before the start of this tax year. Can anyone help please?

**VBAwannabe** · 05-31-2009, 12:42 AM

Hello, maverikk!

If I understand you correctly, something like this may do it:

Sub GetMonthCount()
Dim currentMonth As Long
Dim startMonth As Long

startMonth = 1 'Or whichever month the beginning of the tax year is
currentMonth = Month(Date) 'Returns the number of the current month.

If Month(CDate(Range("MyStart").Value)) > startMonth Then 'Assumes date entered into cell named "MyStart"
    startMonth = Month(CDate(Range("MyStart").Value))
End If

MsgBox currentMonth - startMonth
End Sub

**protonLeah** · 05-31-2009, 01:20 AM

Maybe:
monthsworked.xls

**squiggler47** · 05-31-2009, 01:21 AM

VBA is unnecessary!

Plus since is uk Tax year starts 6/4/2009!

=DATEDIF(MAX(DATE(YEAR(TODAY())-(AND(DAY(TODAY())<6,MONTH(TODAY())<=4)),MONTH(B4),DAY(B4)),DATE(YEAR(TODAY())-(AND(DAY(TODAY())<6,MONTH(TODAY())<=4)),4,6)),TODAY(),"m")

Will give the full months in B4

=DATEDIF(MAX(DATE(YEAR(TODAY())-(AND(DAY(TODAY())<6,MONTH(TODAY())<=4)),MONTH(B4),DAY(B4)),DATE(YEAR(TODAY())-(AND(DAY(TODAY())<6,MONTH(TODAY())<=4)),4,6)),TODAY(),"m")+(DATEDIF(MAX(DATE(YEAR(TODAY())-(AND(DAY(TODAY())<6,MONTH(TODAY())<=4)),MONTH(B4),DAY(B4)),DATE(YEAR(TODAY())-(AND(DAY(TODAY())<6,MONTH(TODAY())<=4)),4,6)),TODAY(),"md")/(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)-DATE(YEAR(TODAY()),MONTH(TODAY()),0)))

will give decimal months

Its slightly complicated due to the tax year starting 6/4 in the uk, if you want to use a different base date change all the 6's to the day the tax year starts for you!

Basically the formula decides which is the biggest, start of taxyear or startdate and uses that as a basis to work on

it then calculates the days since the start of the last month/ days in this month to get the fractional part

**squiggler47** · 05-31-2009, 01:57 AM

VBAWannabe

VBA is a last resort, most users here would rather not use VBA (yes i know this is excel programming,but a lot of new users dont know the difference!)

Often solutions are asked for in VBA that can be done in formulas, and I have yet to find a VBA UDF that performs as fast as a formula, nomatter how complex the formula is!

**DonkeyOte** · 05-31-2009, 05:03 AM

Originally Posted by squiggler47

...I have yet to find a VBA UDF that performs as fast as a formula, nomatter how complex the formula is!

I would certainly advise reading through Charles Williams' discussion on the matter: http://www.decisionmodels.com/calcsecretsj.htm

At a talk he gave recently in London he made the point that UDFs could on occasion be deemed preferential to Arrays when used en masse and/or when referencing large ranges.

**VBAwannabe** · 05-31-2009, 11:16 AM

Originally Posted by squiggler47

VBAWannabe

VBA is a last resort, most users here would rather not use VBA (yes i know this is excel programming,but a lot of new users dont know the difference!)

Often solutions are asked for in VBA that can be done in formulas, and I have yet to find a VBA UDF that performs as fast as a formula, nomatter how complex the formula is!

Point taken, Squiggler, but as the OP didn't specify and this is Excel Programming, I tried to provide a VBA solution. Now, the OP has options. As far as VBA being a last resort, that is a matter of preference, I think. Cheers!

**daddylonglegs** · 05-31-2009, 11:42 AM

Originally Posted by maverikk

What I need to do is calculate the number of months worked by a staff member in this tax year if they enter their start date - taking into account that their start date may be in 2001 (or other) - I need to disregard all information before the start of this tax year. Can anyone help please?

If you want a formula solution then you can approach it this way

To get previous 6th April from today

=DATE(YEAR(TODAY()+270)-1,4,6)

So to calculate whole months from that date (or later employee start date) until today

=DATEDIF(MAX(A1,DATE(YEAR(TODAY()+270)-1,4,6)),TODAY(),"m")

...or do you need a result which rounds to the nearest whole month......or takes partial months into account? Some examples always help, I find.....