Forum Discussion

AlexBennett3's avatar
AlexBennett3
Qrew Trainee
5 years ago

Formula help (Start date + number of days)

Hey everyone,

I'm having an issue getting my formulas to display on a Calendar report correctly. The start date is never 'included' in the count of days for the job duration, so on the Calendar I always get an extra day added. I only know that I could add -1 to the formula to minus a day, but then if that value is 0 because it has no duration, it gives me a -1... or if the result is only 1 day for the job, right now it will display 2 days on the calendar, if I add a -1, the duration will change to 0 on my form. Is there any work around to this? Kind of annoying that the start date isn't included in the count.

Formula:
(ToText(Ceil([1. Sub Duration]/10) & " " & "Days"))

// [1. Sub Duration] is my previous step that gets the final number in hours in a Numeric - Formula Field, divided by 10 for 10 hour workdays.
// Then obviously to result in # Days to display on the form for readability. 

Any help would be appreciated.

Thanks!​

------------------------------
Alex Bennett
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    I suggest that you have one field to calculate the duration in days for display purposes and then use another formula to calculate the end date for the calendar.  The latter will have the minus 1.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • AlexBennett3's avatar
      AlexBennett3
      Qrew Trainee
      5 years ago
      So I attempted to make new formulas to (-1) for my Calendar alone, and now i'm just running into the same problem except on the calendar. My calendar now wont display anything under 1 day because they result in 0 or -1. is there any changes I can make to the formula to fix this?

      New Formula Ex. Created on Schedules side:
      (ToText(Ceil([7. Sub Duration]/8 -1) & " " & "Days"))

      Duration Code on Calendar side:
      ToNumber(Left(Case([Department - Phase], "Fixtures - Sub Rough", [Schedule - Fixtures Sub Duration(Calendar)], "Fixtures - Rough", [Schedule - Fixtures Rough Duration(Calendar)], "Fixtures - Finish", [Schedule - Fixtures Finish Duration(Calendar)], "Fixtures - Close", [Schedule - Fixtures Close Duration(Calendar)]), " " ))

      ------------------------------
      Alex Bennett
      ------------------------------
      • AlexBennett3's avatar
        AlexBennett3
        Qrew Trainee
        5 years ago
        Is there a way to convert a Date field to Workdate? I'm attempting the WeekdayAdd function, but my start date is just the field type date. On the formulas page it says it can be used with Workdate as well? This sounds like my issue as having WeekdayAdd(WorkDate d, Number n) Sounds like it will include the start date as the duration. As WeekdayAdd(Date d, Number n) which I'm currently using, does not. I'm just not sure how to change the type to Workdate in my start date fields. Maybe Formula - Date, and add a certain formula to do so?

        ------------------------------
        Alex Bennett
        ------------------------------