Calculate the number of day

Options
Eric Laitenberger
Eric Laitenberger ✭✭✭✭✭
edited 07/07/22 in Formulas and Functions

Hello,


I would like create a graph with the number of days absence per month added by employees. I have the beginning of absence and the end of absence. Someone can help me please

Answers

  • KC Jones
    Options

    Do you have a dashboard set up for it yet? And a metrics sheet for the project sheet? You will need to have those set up first.

    The way I have done it, is I pull the information needed into the metrics sheet by using a cross sheet formula in the metrics sheet, sourcing from my project sheet. Then I used a SUM function to total it.

    Then I go into the dashboard, edit, and use the metrics sheet as my source sheet.

    I will pull some photos and add them here, as I know that is probably confusing

  • Eric Laitenberger
    Eric Laitenberger ✭✭✭✭✭
    Options

    Hello,

    Sorry I was in vacation.

    Yes, I have a dashboard and metrics sheet.

    Sorry but I dont understand your solution :(

    Yes if you can give me pictures please

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You will need to add a column in the source sheet that calculates the number of days for each absence in the individual rows.


    Then in your metrics sheet you would use a SUMIFS to add up the values in this new column based on the name.


    From there you would add the chart widget to your dashboard and reference the metrics sheet.

  • Eric Laitenberger
    Eric Laitenberger ✭✭✭✭✭
    Options

    Yes but if a collaborator is out of office from 01-JUN-2022 to 23-AOU-22, I would like know the number per month workday

    June =

    July=

    ect...

  • Eric Laitenberger
    Eric Laitenberger ✭✭✭✭✭
    edited 08/02/22
    Options

    Hi, I would like calculate the number of days per month, I just have a period.

    You can fin a print screen which can explain it


    ++


    Eric

  • Brian_Richardson
    Brian_Richardson Overachievers
    Options

    January Column

    =IF(AND(MONTH(Start@row) = 1, MONTH(End@row) = 1) = true, End@row - Start@row, IF(AND(MONTH(Start@row) = 1, MONTH(End@row) > 1) = true, 32 - YEARDAY(Start@row), IF(AND(MONTH(Start@row) < 1, MONTH(End@row) = 1) = true, YEARDAY(End@row))))


    February Column:

    =IF(AND(MONTH(Start@row) = 2, MONTH(End@row) = 2) = true, End@row - Start@row, IF(AND(MONTH(Start@row) = 2, MONTH(End@row) > 2) = true, 60 - YEARDAY(Start@row), IF(AND(MONTH(Start@row) < 2, MONTH(End@row) = 2) = true, YEARDAY(End@row) - 31)))


    And so on... repeat the formula for each column (set as a column formula) by adjusting the month number and the YEARDAY numbers for each column. YEARDAY gives you the number of the day in the year 1-365.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!