Numbers of days per month from a timeline

Platform discussions Special workflows & use cases
1

Hello, From a timeline i would like to calculate the number of days per month present in the timeline. Exemple of what i would like to achieve:
image
If you have an idea of the formula to use? thanks

1 Like
2

I succeed doing it! Exemple of formulas for July
IF(

AND(DATEVALUE({Échéance Pose#Start})<DATEVALUE(“07/01/2023”), AND((DATEVALUE({Échéance Pose#End})>=DATEVALUE(“07/01/2023”))), DATEVALUE({Échéance Pose#End})<DATEVALUE(“08/01/2023”)), WORKDAYS({Échéance Pose#End},“07/01/2023”),

IF(

AND(DATEVALUE({Échéance Pose#Start})>=DATEVALUE(“07/01/2023”),(DATEVALUE({Échéance Pose#End})<DATEVALUE(“08/01/2023”))),

IF(DAYS({Échéance Pose#End},{Échéance Pose#Start})+1 > 5 , WORKDAYS({Échéance Pose#End},{Échéance Pose#Start}), DAYS({Échéance Pose#End},{Échéance Pose#Start})+1),

IF(

AND(DATEVALUE({Échéance Pose#End})>=DATEVALUE(“08/01/2023”), AND((DATEVALUE({Échéance Pose#Start})>=DATEVALUE(“07/01/2023”))), DATEVALUE({Échéance Pose#Start})<DATEVALUE(“08/01/2023”)), WORKDAYS(“07/31/2023”, {Échéance Pose#Start}),

IF(

AND(DATEVALUE({Échéance Pose#Start})<DATEVALUE(“07/01/2023”),(DATEVALUE({Échéance Pose#End})>=DATEVALUE(“08/01/2023”))), WORKDAYS(“07/31/2023”,“07/01/2023”), 0))))

3

Hi Dessus,

I’m trying to calculate Workdays like this but this formula isn’t working. Are you pulling the information for other columns or from the timeline column itself?

4

Dear Brandon,
the {Échéance Pose} is my timeline column. {Échéance Pose#Start} is my start time. and the {Échéance Pose#End} is my end time. I hope it helps

5

Thanks for the reply @Joachim I was using this as a base to calculate the cost of the employee on a project for a particular month and adapted it to do so below. Then rounded to 2 decimal points. Appreciate the assistance with the above number of working days calculation.

ROUND((({Actual Effort}*{Daily Rate})*IF(
AND(
DATEVALUE({Timeline#Start}) < DATEVALUE(“07/01/2024”),
DATEVALUE({Timeline#End}) >= DATEVALUE(“07/01/2024”),
DATEVALUE({Timeline#End}) < DATEVALUE(“08/01/2024”)
),
WORKDAYS({Timeline#End}, “07/01/2024”),

IF(
AND(
DATEVALUE({Timeline#Start}) >= DATEVALUE(“07/01/2024”),
DATEVALUE({Timeline#End}) < DATEVALUE(“08/01/2024”)
),
IF(
DAYS({Timeline#End}, {Timeline#Start}) + 1 > 5,
WORKDAYS({Timeline#End}, {Timeline#Start}),
DAYS({Timeline#End}, {Timeline#Start}) + 1
),

IF(
  AND(
    DATEVALUE({Timeline#End}) >= DATEVALUE("08/01/2024"),
    DATEVALUE({Timeline#Start}) >= DATEVALUE("07/01/2024"),
    DATEVALUE({Timeline#Start}) < DATEVALUE("08/01/2024")
  ), 
  WORKDAYS("07/31/2024", {Timeline#Start}),

  IF(
    AND(
      DATEVALUE({Timeline#Start}) < DATEVALUE("07/01/2024"),
      DATEVALUE({Timeline#End}) >= DATEVALUE("08/01/2024")
    ), 
    WORKDAYS("07/31/2024", "07/01/2024"), 
    0
  )
)

)
)), 2)

6

Dear Brandon,
I’m not expert in formulas, could you please retry to copy your formula? I see in your last post, two parts in your formula. One in white, one in pink, not sure wich one is the correct, or if I have to choose all of it.
You may also try with different date, using YYYY-MM-DD. I’m not sure as i don’t know what is your exact issue.

7

Hi @Joachim, @bmarsh,

Here is another (and a bit simpler) way to do it.

Formula for Oct 2024:

MAX(0,MIN(1 + DAYS(IF("2024-10-31">FORMAT_DATE({Timeline#End},"YYYY-MM-DD"),FORMAT_DATE({Timeline#End},"YYYY-MM-DD"),"2024-10-31"),IF("2024-10-01">FORMAT_DATE({Timeline#Start},"YYYY-MM-DD"),"2024-10-01",FORMAT_DATE({Timeline#Start},"YYYY-MM-DD")))))

 
 

2024-10-25_14-31-56
2024-10-25_14-31-56566Ă—147 18.8 KB

I used DAYS, but it works the same with WORKDAYS.

Hope it helps.

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

1 Like
8

Thanks @GCavin .
It works fine!
I tested with workdays you have also to remove the “1 +”

MAX(0,MIN(WORKDAYS(IF("2024-10-31">FORMAT_DATE({Timeline#End},"YYYY-MM-DD"),FORMAT_DATE({Timeline#End},"YYYY-MM-DD"),"2024-10-31"),IF("2024-10-01">FORMAT_DATE({Timeline#Start},"YYYY-MM-DD"),"2024-10-01",FORMAT_DATE({Timeline#Start},"YYYY-MM-DD")))))
1 Like
9

Thanks Dessus. Just seeing this. I was able to get it to work with the code posted.

1 Like