Former Member

# Calculate number of weekdays from the first of the month to current date

I am trying to create a comparative report that shows how many hours an employee has logged versus the number of hours they are expected to have logged to that point in the month. Expected hours are only calculated against weekdays.

What I'm trying to figure out is how to get a numeric representation of the weekdays so that I can multiply by the expected 8 hours an employee would have put in for the day. So, for example, in December 2008, if I run the report on 12-10-2008, it would return 8 weekdays and the expected number of hours would be 64.

Any assistance would be greatly appreciated.

10|10000 characters needed characters exceeded

### Related questions

Former Member
Posted on Dec 10, 2008 at 09:38 PM

Hi Justin,

This is not easy to do as there are bank holidays to consider. I would generally implement a Data Lookup table in the source and use that to get values like number of working days since start of month.

Failing that you can get the number of the day of the month and do a formula like

`Truncate(ToNumber(FormatDate(CurrentDate();"dd"))/7;0)*5+Mod(ToNumber(FormatDate(CurrentDate();"dd"));7)`

Regards

Alan

10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 10, 2008 at 10:06 PM

Hi Justin,

Excellent Solution by Alan but i am afraid, this solution doesn't work when first day of the month (like 1st Nov, 1st Jun starts with Saturday or Sunday which is not a weekday) in these cases you will get some difference.

Thanks...

Pratik

10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 10, 2008 at 09:58 PM

We actually track the Holiday hours, so this solution worked perfectly.

10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 10, 2008 at 10:09 PM

this solution doesn't work when first day of the month (like 1st Nov, 1st Jun starts with Saturday or Sunday which is not a weekday) in these cases you will get some difference.

10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 10, 2008 at 10:09 PM

Any suggestions on what to do in those instances? Is there one formula that would work regardless of what day the first day of the month falls on?

10|10000 characters needed characters exceeded
• Former Member

Hi Justin,

I had a look at this, and have not managed to come up with a formula. I think the Date_Looup table might be your only hope

Sorry

Alan

• Former Member
Posted on Dec 12, 2008 at 04:55 PM

I'm not exactly sure how to use the Data Lookup you refer to. Any assistance you could offer in that direction would be appreciated.

10|10000 characters needed characters exceeded
• Former Member

Hi Justin,

A Date Lookup table is created in your database. In it you can include fields that suit your purpose.

For instance

Date_Lookup

(

Date_ID Date,

Day Number(2),

Month Number(2),

Year Number(4),

Day_desc varchar2(10),

Month_desc varchar2(10),

Is_Working_Day char(1),

)

You then populate it will a script and link it to you table date field.

Regards

Alan

Edited by: McClean Alan on Dec 12, 2008 8:52 PM

• Former Member
Posted on Dec 22, 2008 at 05:50 PM

Just checking to see if anyone else has any suggestions on this? The simpler the better.