0

I have a calculated column that returns days open for job listings.

=DATEDIF([Requisition Open Date],TodayDate,"D")-IF(WEEKDAY(TodayDate)=7,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2)+IF(WEEKDAY([Requisition Open Date])=7,2,1)

This is great and works well but doesn't take into account if a Candidate starts so it continues to count.

I created another column and used this formula: =IF(ISBLANK([Candidate Start Date]),"",[Candidate Start Date]-[Requisition Open Date]+1) which only adds the day a requisition took to fill.

My question. Is it possible to use an if/or statement on this? I created a hybrid but only get a #Value! in the column:

=IF(OR(ISBLANK([Candidate Start Date]),"",[Candidate Start Date]-[Requisition Open Date]+1),DATEDIF([Requisition Open Date],TodayDate,"D")-IF(WEEKDAY(TodayDate)=7,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2)+IF(WEEKDAY([Requisition Open Date])=7,2,1))

Thanks

1

Not sure if I have understood you correctly, but do you want to stop counting the days if Candidate Start Date has a value? If so, try this one instead:

=IF(ISBLANK([Candidate Start Date]),DATEDIF([Requisition Open Date],TodayDate,"D")-IF(WEEKDAY(TodayDate)=7,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2)+IF(WEEKDAY([Requisition Open Date])=7,2,1),[Candidate Start Date]-[Requisition Open Date]+1)

Note:

If A = DATEDIF([Requisition Open Date],TodayDate,"D")-IF(WEEKDAY(TodayDate)=7,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2,FLOOR((DATEDIF([Requisition Open Date],TodayDate,"D")+WEEKDAY([Requisition Open Date]))/7,1)*2)+IF(WEEKDAY([Requisition Open Date])=7,2,1)

Then the formula is

=IF(ISBLANK([Candidate Start Date]),A,[Candidate Start Date]-[Requisition Open Date]+1)
2
  • Chelsea_MSFT thanks very much. Stop counting the days is exactly what I was going for. Just plugged the formula in and it worked like a charm. I really appreciate the help. Feb 26 at 9:47
  • @msm02eshift Please, don't forget to mark the answer as accepted in case it helped you! Feb 26 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.