# Thread: Split the Monthly values equally based on calendar

1. Registered User
Join Date
Oct 2013
Posts
165

## Unanswered: Split the Monthly values equally based on calendar

Dear Members,

I have a table in access which contains the Monthly Planned Man-hours of Employees. Al so I have a Project Calendar showing the Project dates on Weekly Basis.

Now I want to create a query in such a way that my monthly man-hours should be equally splitted into weekly values based on the number of weeks.

For ex I have 160 Hours/Month, In January I have five weeks so it should divide 160 by five and show the values in WK01 to WK05. If I have four weeks then it should be divided by 4 and values should be from WK01 to WK04.

I had done the above using manually for every months, however I want to do the same automatically since I have 36 months of data for each and every person. The Objective is to track the status of Man hours Plan vs Actual for all the employees in a Project.

I will appreciate if any body can share me a useful template to track the man hours booked by Each employee on weekly basis.

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
He this should get you thinking

I would write some function and you just have to pass the right data in it

Code:
```Function st1month(Tdate)
st1month = DateSerial(Year(Tdate), Month(Tdate), 1)
End Function

Function LastDay(Tdate)
End Function

Function WeeksInMonth(indate As Date)
Dim startdate As Date
Dim Enddate As Date
startdate = st1month(indate)
NumberOfweeksInThisMonth = "WK" & FORMAT(DateDiff("ww", startdate, indate),"00")
End Function

Public Function WEEKEND(dat) As Date
If IsNull(dat) Then Exit Function
dat = DateSerial(Year(dat), Month(dat), Day(dat))
If dat Mod 7 > 0 Then
WEEKEND = dat - dat Mod 7 + 7
Else
WEEKEND = dat
End If
End Function```
Paste it into a module

then in a query

MonthWK: WeeksInMonth([datefeildnameinhere]) return WEEK Number of THAT month pass in as a datevalue

it will return WK01 and WK01 for differance months

if you change

NumberOfweeksInThisMonth = "WK" & FORMAT(DateDiff("ww", startdate, indate),"00")

to

NumberOfweeksInThisMonth = "WK" & FORMAT(DateDiff("ww", startdate, indate),"00") & "-" & format(indate,"mmm YYYY")

should return WK01-Jan 2015

what happen

on the 30th and 31st of march and they in the march figs of in april figs

what i do is when reporting I put everything into a WEEKEND Date

so 30th and 31st march fall in on the april 4th weekend date Which then comes April figs
Last edited by myle; 03-09-15 at 23:36. Reason: spelling

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
sorry
all this goes back to your ealry machinations with a calendar
had you designed your calednar table the way it was suggested you can derive all this sort of stuff
a calendar table matchs up true dtaes with financial or accoutnign or production dates or whatever other dates you need
you can define whatever structure you need.
ferinstance I often have to deploy things based on accounting calendars
where youd have something like

Code:
```Fyear           the accounts year (so it could be 2015, or 2016 or whatever they feel comfortable with
MajorPeriod   an identifier usually runs from the  first month in the accounting year
MinorPeriod   an identifier, usually the week number (depends on customer needs but that could be from wk1 to wk 52/3, OR 1 to 4/5 each month
StartsOn      a datetime column representing the start of this period```
you could just as easily use an end on datetime, but don't be tempted to use both. defacto the start of the next period is the end of the previous period. if you store both then you are creating a situation where dates can disappear and or overlap. yes you can code in your form to validate this, but int he Access /JET wordl you cannot impose that sort of checking at table level, where it actually matters. yes you probably could use stored procedures but there is a limit to what a non specialist can use.

to find the accounting period you do a join in a query and that value can be used to do this sort of grouping
the real advantage is that there is no other code to maintain. so if reports get screwed up because of date issues is not a developer issue its the users issue because they have forgotten to sort out their data in time. there is no expectation that you as a developer have to drop everything usually at peak workload. also making changes under pressure is a sure fire way of either making a mistake (not realising the complexity of the problem or impact of the change), or putting undue pressure on workloads. |Although to be fair when developer resource is allocated to Accounting department issues its amazing how 'generous' accountign budgets are to help you pull them form out of the sticky smelly brown stuff

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•