Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
8 years ago

How to exclude weekend days from a date formula field

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?
Formulas and functions

12 Replies

Sort By
  • KevinO_Boyle's avatar
    KevinO_Boyle
    Qrew Cadet
    8 years ago
    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))
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    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. 
  • KevinO_Boyle's avatar
    KevinO_Boyle
    Qrew Cadet
    8 years ago
    OK - sorry, ignore me - I read the question too fast.  the equation above is for excluding weekends from a calculation of duration.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    8 years ago
    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. :)
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      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))
    • ArchiveUser's avatar
      ArchiveUser
      Qrew Captain
      8 years ago
      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! :(
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      When  you say that it did not work,  was there a syntax error or you did not get there result that you expected?
  • ChrisHutchens's avatar
    ChrisHutchens
    Quickbase Staff
    7 years ago
    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.