Calulating Sums with Date issue

[etrain]

I need to calulate hours logged on a semi-annual basis based on a persons birth month. For example employee Jim has a birth month of October. I need to calulate his hours logged from Nov to Apr (1st Semi) and again from Apr to Nov (2nd Semi). It needs to be done on a adhoc basis through out the year so if its Dec 25th I would get his 1st semi annual total from Nov 1 to Dec 25th and nothing for his 2nd becuase there is nothing logged in the period.

I have tried a between statement where HoursLogged >DateAdd(M,1,BirthMonth) and <=DateAdd(M,6,BirthMonth).
Doesnt work becuase if his birth year is 1975 it fails. It adds the months correctly it just adds with year 1975 not current year.
If I parse out the date to a numeric value as in this case I get 10. If you add 6 to it you get 16 not 4 which would be April. The birth year doesnt matter only the current year and previous year in most cases.

I am looking to do this either in a query or if need be VBA to calculate it. It all makes sense in my head and 10 years ago I might have had an easier time with it

Ideas please. Thanks again.

Remember the Past, Plan for the Future, yet Live in the Present for tomorrow may never come.
-etrain

 

[SkipVought]

Hi,

Use this for the target date in your DateAdd() function...

DateSerial(Year(Date()),Month(BirthDate),1)

 

[etrain]

Thanks this got me in the right direction.

Remember the Past, Plan for the Future, yet Live in the Present for tomorrow may never come.
-etrain

 

[etrain]

Still having issues with making this more dynamic. This is all from excel in formulas and so this thread really needs to be moved as well. Not sure how to accomplish that.

So here is an examples and the expected results. Everything except the birth month needs to be dynamic.
Birth Month = 1 JAN 1975
As of Date (normally current date) = 29 JAN 2015
Semi Annual 1 = 1 FEB 2014 till 31 JUL 2014

​

Start = EDATE(DATE(YEAR(As of Date),MONTH(BirthMonth),1),1)

​

End = EOMONTH(Start,5)
Semi Annual 2 = 1 AUG 2014 till 31 JAN 2015

​

Start = EDATE(DATE(YEAR(As of Date),MONTH(BirthMonth),1),7)

​

End = EOMONTH(EDATE(DATE(YEAR(As of Date),MONTH(BirthMonth),0),6),0)

This works as long as the "As Of Date" is February. If I switch it to January 2015 it fails to the test.

Example 2
Birth Month = 1 MAR 1975
As of Date (normally current date) = 3 FEB 2015
Semi Annual 1 = 1 FEB 2015 till 31 JUL 2015
Start = EDATE(DATE(YEAR(As Of Date),MONTH(BirthMonth),1),-11)
End = EOMONTH(EDATE(DATE(YEAR(Start),MONTH(BirthMonth),0),6),0)
Semi Annual 2 = 1 AUG 2015 till 31 JAN 2016
Start = EDATE(DATE(YEAR(As Of Date),MONTH(BirthMonth),1),-11)
End = EOMONTH(EDATE(DATE(YEAR(Start),MONTH(BirthMonth),0),6),0)

This works as long as the "As Of Date" is February. If I switch it to April 2015 it fails to the test.

So both kind of work but not for every possible birth month and not for every month through out the year.

Any help would be appreciated. Even if it is a complete revamp that is fine.



Remember the Past, Plan for the Future, yet Live in the Present for tomorrow may never come.
-etrain