How to exclude weekend days from a date formula field

  • 0
  • 1
  • Question
  • Updated 12 months ago
  • Acknowledged
I have an old calculation that either I haven't been paying attention to in my tasks or seems to all of a sudden stopped working. I have a date formula field called Start Date with Check that is supposed to bring up the Start Date field but calculate a new date that is not on the weekend (so the next weekday) and this date field figures into several other date calculations. However, I noticed it is returning weekend days.

This is the formula I currently have: If(DayOfWeek([Start Date])=0, ToWorkDate(ToWeekdayN([Start Date])), ([Start Date]))

Any idea where I may be off?
Photo of Dawn Rene

Dawn Rene

  • 404 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
Give this a shot.

var Date StartTime = [Booked Date];
var  Number  NumWeekendDays = ToDays([SCHEDULED DATE] - ($StartTime))  - WeekdaySub ( [SCHEDULED DATE], ($StartTime) );

If([XMF Current Production Schedule Date]>[SCHEDULED DATE],ToDays([XMF Current Production Schedule Date]-[Booked Date])-($NumWeekendDays),ToDays([SCHEDULED DATE]-[Booked Date])-($NumWeekendDays))
Photo of Dawn Rene

Dawn Rene

  • 404 Points 250 badge 2x thumb
Well, that seems a lot more complicated than I expected with fields that I don't have and don't want to create if I don't have to. 
Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
OK - sorry, ignore me - I read the question too fast.  the equation above is for excluding weekends from a calculation of duration.
Photo of Dawn Rene

Dawn Rene

  • 404 Points 250 badge 2x thumb
No worries. It's a Friday afternoon, lol. But good to know for future applications. Just need and actual date that shows up that is not a weekend based on whatever the Start Date is. :)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Try this

WeekdayAdd([Start Date],0)
Photo of Dawn Rene

Dawn Rene

  • 404 Points 250 badge 2x thumb
First one worked perfectly, 2nd one gave me an error. But now I'm trying to filter out holidays for my Estimated Started Date (which was just a Work Date formula field, [Start Date with Check]} and tried this calc to add a day after the holidays and this didn't work: 
If([Start Date with Check]=ToDate("01-01-2018"),WeekdayAdd([Start Date with Check],1),
[Start Date with Check]) 

yikes I'm off somewhere again! :(
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
When  you say that it did not work,  was there a syntax error or you did not get there result that you expected?
Photo of Dawn Rene

Dawn Rene

  • 404 Points 250 badge 2x thumb
There was a syntax error.  It says the function is defined to be ToWorkDate (Date d).
The Start Date field is already a Work Date field and the Start Date with Check formula field is a Work Date field so maybe I don't need to add the ToWorkDate?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
Right, if they are already Workdate field types, then you do not need to convert them to Work Dates.   

But a "work date" field type is actually an unusual field type and only used with predecessors.  If you don't know about predecessors, it sounds like you may have got yourself all tangled up needlessly in Work Date field types and you should not be using Work Date formuals at all.
Photo of Dawn Rene

Dawn Rene

  • 404 Points 250 badge 2x thumb
I am using predecessors. :)