Skip to main content

Separating WORKDAY_DIFF by month?

  • September 2, 2020
  • 2 replies
  • 46 views
A

Hi! I have two Dates columns, “START” and “END” that specify the start and end dates for a particular task. I have another column that has a WORKDAY_DIFF formula that calculates the working days between these two dates to determine how many days it will take to accomplish this task. However, if “START” and “END” were across different months, how would I separate the working days spent by month?

For example, if the “START” of the task was 10/30/2020 and the “END” of the task was 11/5/2020, the WORKDAY_DIFF between these two dates is 4.0. However, how do I specify/separate the WORKDAYS spent in October (1.0) and the WORKDAYS spent in November (3.0)? Is there a formula, or way I can structure my table to identify how many days per month a task will cost?

    2 replies

    K
    • Kris11
    • Inspiring
    • September 2, 2020

    Hi AISYAH_AMBOK,

    Have you seen the function: DATETIME_DIFF() ?

    Give this a try:

    DATETIME_DIFF(
  {Start Date},
  {End Date},
  'days'
)

    WORKDAY_DIFF is only looking at the day of the weeks and comparing them, so you’ll always get 0-6 in results based on the days of the week being compared. DATETIME_DIFF() is what you’re looking for and should work the way you’re trying to use it. :slightly_smiling_face:

    A
    • AISYAH_AMBOK
    • Author
    • New Participant
    • September 3, 2020

    Hi AISYAH_AMBOK,

    Have you seen the function: DATETIME_DIFF() ?

    Give this a try:

    DATETIME_DIFF(
  {Start Date},
  {End Date},
  'days'
)

    WORKDAY_DIFF is only looking at the day of the weeks and comparing them, so you’ll always get 0-6 in results based on the days of the week being compared. DATETIME_DIFF() is what you’re looking for and should work the way you’re trying to use it. :slightly_smiling_face:


    Hi Kris! Thank you for the response!

    However, it’s not exactly the solution I’m looking for. :grinning_face_with_sweat: I’m sorry, I think I may not have explained myself properly. Below is a screenshot of the table I’m testing this out on:

    I have the two columns indicating the “START” and “END” dates for a task. If the start & end occur within the same month, calculating working days it takes to finish the task is pretty straightforward.

    However, as soon as the end date crosses over into a different month, I’m not sure how to separate the tally of the working days by month.

    The end result I’m looking for would be something like: Oct 2020, 1.0; Nov 2020, 3.0

    Just FYI, the formula I have in the “DAYS SPENT PER MONTH” column is:

        IF(IS_SAME(
    {MONTH TASK STARTS},
    {MONTH TASK ENDS},
    'month'),
    ({MONTH TASK STARTS}
    &", "&{ALLOCATION}),
    IF(IS_AFTER({MONTH TASK ENDS},
    {MONTH TASK STARTS})=1,
    "CROSS MONTH"))
    Terms & ConditionsAccessibility statement