# Calculate the number of day

Options
✭✭✭✭✭
edited 07/07/22

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

• ✭✭
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

• ✭✭✭✭✭
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

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭
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...

• ✭✭✭✭✭
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

• 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!