Calculate the number of day

Eric Laitenberger

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

KC Jones

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

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

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

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

formula number of days per month.docx

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

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.