Skip to Content
author's profile photo Former Member
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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

7 Answers

  • Best Answer
    author's profile photo Former Member
    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 10, 2008 at 09:58 PM

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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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?

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    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.

    Add a comment
    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

  • author's profile photo Former Member
    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.