Discussions

 View Only
Expand all | Collapse all

How to exclude weekend days from a date formula field

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training12-08-2017 21:28

  • 1.  How to exclude weekend days from a date formula field

    Posted 12-08-2017 20:57
    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?


  • 2.  RE: How to exclude weekend days from a date formula field

    Posted 12-08-2017 20:58
    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))


  • 3.  RE: How to exclude weekend days from a date formula field

    Posted 12-08-2017 21:21
    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. 


  • 4.  RE: How to exclude weekend days from a date formula field

    Posted 12-08-2017 21:24
    OK - sorry, ignore me - I read the question too fast.  the equation above is for excluding weekends from a calculation of duration.


  • 5.  RE: How to exclude weekend days from a date formula field

    Posted 12-08-2017 21:26
    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. :)


  • 6.  RE: How to exclude weekend days from a date formula field

    Posted 12-08-2017 21:28
    Try this

    WeekdayAdd([Start Date],0)


  • 7.  RE: How to exclude weekend days from a date formula field

    Posted 12-08-2017 21:31
    If you need the result to be a Work Date field type due to using Predecessors, then it would need to be

    ToWorkDate(WeekdayAdd([Start Date],0))


  • 8.  RE: How to exclude weekend days from a date formula field

    Posted 12-11-2017 21:47
    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! :(


  • 9.  RE: How to exclude weekend days from a date formula field

    Posted 12-11-2017 22:58
    When  you say that it did not work,  was there a syntax error or you did not get there result that you expected?


  • 10.  RE: How to exclude weekend days from a date formula field

    Posted 12-12-2017 16:26
    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?


  • 11.  RE: How to exclude weekend days from a date formula field

    Posted 12-12-2017 16:41
    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.


  • 12.  RE: How to exclude weekend days from a date formula field

    Posted 12-12-2017 16:46
    I am using predecessors. :)


  • 13.  RE: How to exclude weekend days from a date formula field

    Posted 03-21-2019 13:18
    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.