# How to exclude weekend days from a date formula field

• 0
• Question
• Updated 3 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?
• 434 Points

Posted 2 years ago

• 0
• 602 Points
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))
• 434 Points
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.
• 602 Points
OK - sorry, ignore me - I read the question too fast.  the equation above is for excluding weekends from a calculation of duration.
• 434 Points
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. :)

QuickBaseCoach App Dev./Training, Champion

• 69,572 Points
Try this

QuickBaseCoach App Dev./Training, Champion

• 69,572 Points
If you need the result to be a Work Date field type due to using Predecessors, then it would need to be

• 434 Points
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! :(

QuickBaseCoach App Dev./Training, Champion

• 69,572 Points
When  you say that it did not work,  was there a syntax error or you did not get there result that you expected?
• 434 Points
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?

QuickBaseCoach App Dev./Training, Champion

• 69,572 Points
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.
• 434 Points
I am using predecessors. :)

Chris Hutchens, Quick Base Senior Product Manager, Mobile

• 660 Points
It sounds like you got it figured out, but another formula component I find very useful in similar situations is IsWeekday(date d), which returns true if d is a weekday, otherwise false.