Skip to main content

Hi! I am trying to create monthly columns that tell me if a staff worked the entire month, a portion of month or not at all. For example… staff 1 starts 9/8- 11/12 - my formula would show staff 1 worked 73% of the month. Same if staff 2 starts 8/31-9-1…would work 3% of september. I have it almost working…but issue with start date on the first of the month. Any ideas?


Thanks!

Matt


IF(

AND(

IS_BEFORE({Start Date},‘9-1-2019’),IS_BEFORE({End Date},‘9-1-2019’)),0,


IF(

AND(

OR(IS_SAME({Start Date},‘9-1-2019’),IS_BEFORE({Start Date},‘9-1-2019’)),IS_BEFORE({End Date},‘10-1-2019’)),DATETIME_DIFF(DATEADD({End Date},1,‘days’),‘9-1-2019’,‘days’)/

DATETIME_DIFF(‘10-1-2019’,‘9-1-2019’,‘days’),IF(AND(IS_BEFORE({Start Date},‘9-1-2019’),IS_BEFORE(‘9-30-2019’,{End Date})),1,


IF(AND(IS_BEFORE(‘9-1-2019’,{Start Date}),IS_BEFORE(‘9-30-2019’,{End Date})),(DATETIME_DIFF(‘10-1-2019’,{Start Date},‘days’))/(DATETIME_DIFF(‘10-1-2019’,‘9-1-2019’,‘days’)),


IF(AND(IS_BEFORE(‘8-31-2019’,{Start Date}),IS_BEFORE({End Date},‘10-1-2019’)),DATETIME_DIFF(DATEADD({End Date},1,‘days’),{Start Date},‘days’)/DATETIME_DIFF(‘10-1-2019’,‘9-1-2019’,‘days’),


IF(AND(IS_AFTER({Start Date},‘9-30-2019’),IS_AFTER({End Date},‘9-30-2019’)),0))))))

I am not sure how your tables are structured, but this would be much simpler if you created a new table with a rollup field. Link the new table (call it 😎 with the table with the days worked in A on a start month field (Dec. 19, Jan. 20, Feb. 20, etc. would need to be the format so this will work after a year). Use a formula field in A to count the days for that record. Use a rollup field in B pointed at the day count and use the sum formula to get the total days for each month. Wrap the sum formula in a simple /30 or use another formula field to get the percentage.


If your table structure is fixed, you are above my head. I hate complex formulas and find it much easier to do the necessary changes in table structure rather than fighting for days on where I put the wrong “)”.

Played with it again this morning - this seems to work for me!


IF((DATETIME_DIFF(DATEADD(‘9-30-2019’,1,‘d’),{Start Date},‘d’)/DATETIME_DIFF(DATEADD(‘9-30-2019’,1,‘d’),‘9-1-2019’,‘d’))>=0,(DATETIME_DIFF(DATEADD(‘9-30-2019’,1,‘d’),{Start Date},‘d’)/DATETIME_DIFF(DATEADD(‘9-30-2019’,1,‘d’),‘9-1-2019’,‘d’)),0)


Just need to change the hard keyed dates per each column.


Thanks!

Played with it again this morning - this seems to work for me!


IF((DATETIME_DIFF(DATEADD(‘9-30-2019’,1,‘d’),{Start Date},‘d’)/DATETIME_DIFF(DATEADD(‘9-30-2019’,1,‘d’),‘9-1-2019’,‘d’))>=0,(DATETIME_DIFF(DATEADD(‘9-30-2019’,1,‘d’),{Start Date},‘d’)/DATETIME_DIFF(DATEADD(‘9-30-2019’,1,‘d’),‘9-1-2019’,‘d’)),0)


Just need to change the hard keyed dates per each column.


Thanks!


ACTUALLY - Here is the solution…


IF((DATETIME_DIFF(DATEADD(‘11-30-2019’,1,‘d’),{Start Date},‘d’)/DATETIME_DIFF(DATEADD(‘11-30-2019’,1,‘d’),‘11-1-2019’,‘d’))<0,0,IF((DATETIME_DIFF(DATEADD(‘11-30-2019’,1,‘d’),{Start Date},‘d’)/DATETIME_DIFF(DATEADD(‘11-30-2019’,1,‘d’),‘11-1-2019’,‘d’))>=1,1,(DATETIME_DIFF(DATEADD(‘11-30-2019’,1,‘d’),{Start Date},‘d’)/DATETIME_DIFF(DATEADD(‘11-30-2019’,1,‘d’),‘11-1-2019’,‘d’))))

Terms & Conditions