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

[Former Member]

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.

[Former Member · Accepted solution]

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

[Former Member]

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

[Former Member]

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.

[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]

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?

[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]

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.

[Former Member]

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

[Former Member]

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