Workday Function


Earlier this week I received a request for help from Nancy. She was having a problem with a date formula. She wanted to subtract 8 days from the current date.

Well that’s easy enough. =Today()-8  Since the day I wrote this post is Sept 25th, the result is September 17th (8 days prior).

A
B
C
D
Result
Formula
Current Date
9/25/2012
9/17/2012
=TODAY()-8

However she wanted to only count business days (Nancy did not want to include Saturday or Sunday).

So that changes the formula.

I needed to look only at Workdays. Fortunately Excel has just the function for this.
=WORKDAY(TODAY(),-8)

Since the day I wrote this post is Sept 25th, the result is September 13th (8 workdays prior).


A
B
C
D
1
Result
Formula
Current Date
9/25/2012
2
9/17/2012
=TODAY()-8


3




4
9/13/2012
=WORKDAY(TODAY(),-8)



So what does the WORKDAY function do?

Well according to the Excel Help… Workday returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.”

Now what is really handy here is the ability to identify holidays.

Syntax
WORKDAY(start_date,days,holidays)

Start_date     is a date that represents the start date.

Days     is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.

Holidays     is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of the serial numbers that represent the dates.

Remarks from Microsoft…

Important   Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text .

Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

If any argument is not a valid date, WORKDAY returns the #VALUE! error value.

If start_date plus days yields an invalid date, WORKDAY returns the #NUM! error value.

If days is not an integer, it is truncated.

4 comments:

  1. Hi Bro,
    THANKS SO MUCH for sharing this! I would love to buy you a coffee since I now won’t be up all night that has been driving me crazy (until now!!). I just wish I knew what was going wrong but so glad it’s in the right place now! Thanks again:)

    We have a requirement in checklist functionality for terminations like

    We are using actual termination date is a trigger event from 'No Value' to 'Any Value' and the checklist are are generating automatically.

    Here we have a requirement that when we remove the actual termination date the existing checklist should remove automatically (though if it is in middle of the approval process) and when we enter the actual termination date new checklist should generate again.

    Anyways great write up, your efforts are much appreciated.
    Thank you,
    Hareesh

    ReplyDelete
  2. Hi There,


    You make learning and reading addictive. All eyes fixed on you. Thank you being such a good and trust worthy guide.

    will like to check are there any DBI items that store the previous 'adjustment' amount for the salary components for FF. Or are there any methods/ways that I should look into to retrieve all the salary components and sum it all up to have an absolute salary component amount?
    It seems that CMP_ASSIGNMENT_RGE_SALARY_CHANGE_AMOUNT[N] only stores the latest 'adjustment' amount.
    By the way do you have any YouTube videos, would love to watch it. I would like to connect you on LinkedIn, great to have experts like you in my connection (In case, if you don’t have any issues).
    Please keep providing such valuable information.


    MuchasGracias,
    Luada

    ReplyDelete
  3. Salaam,


    Hip Hip Hooray! I was always told that slightly slow in the head, a slow learner. Not anymore! It’s like you have my back. I can’t tell you how much I’ve learnt here and how easily! Thank you for blessing me with this effortlessly ingestible digestible content.

    We have views which has quoted column names like below. Since R12.2 is not going to support these , how do you recommend us to change these
    workday certification in USA column names without affecting the reports which are based on these view. ?

    Please recommend how to make this change.

    CREATE OR REPLACE FORCE VIEW hr_mc_demo_range
    (
    "Employee Name",
    "Employee No",
    "Asg No",
    "Person Type",
    "Prefix",
    "Exempt or Non-Exempt",
    "Job Name",
    "Job Full Name",
    "Position Name",
    "Position No",
    "Job No",
    "Unit"
    )
    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!


    Best Regards,



    ReplyDelete
  4. Ni Hau,


    Great info! I recently came across your blog and have been reading along.
    I thought I would leave my first comment. I don’t know what to say except that I have

    yes we can change from manual to automatic and automatic to manual.
    work structures--->organization--->description--->select your business group--->goto classification---->choose business group--->
    --->click on others---->window will get opened again click on it------>go to employee num and change from automatic to manual and save. Workday training USA

    if employee number is manual and you want to change it to automatic
    run concurrent program called"change person number to automatic"there in the parameters you can select employee/applicant/contingent worker





    Once again thanks for your tutorial.


    Obrigado,
    Ajeeth

    ReplyDelete