Please Help! Complex date formula

[Matthew_Waters]

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))))))

[Josh_Cooper]

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 B) 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 “)”.

[Matthew_Waters]

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!

[Matthew_Waters · Accepted solution]

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’))))