Discussions

 View Only
Expand all | Collapse all

Null Date Field

  • 1.  Null Date Field

    Posted 11-13-2020 14:24
    Hey Everyone,

    I wanted to see if I could get some Formula advice. I have a formula that takes a start date and adds duration:

    WeekdayAdd([Sub Start Date], ToNumber([Gas Pipe Total Duration])-1)

    I had to include the -1 because I noticed it starts the count after the start date, but doesn't include the start date. 
    But if the total duration is blank or 0 how can I have it not display a date? Right now it's showing the day after start date because of the -1, or just the start date if I remove the -1.

    Any tips on this formula?

    Thanks,

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


  • 2.  RE: Null Date Field

    Posted 11-13-2020 19:52
    try this


    IF(Nz(ToNumber([Gas Pipe Total Duration])<>0,
    WeekdayAdd([Sub Start Date], ToNumber([Gas Pipe Total Duration])-1))

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Null Date Field

    Posted 11-16-2020 10:07
    Hey Mark, I'm getting an error that is saying 'expecting bool, but found number'

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



  • 4.  RE: Null Date Field

    Posted 11-16-2020 10:09
    Can you post the whole error message you get when you save, including the part where it repeats back and underlines the problem?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Null Date Field

    Posted 11-16-2020 10:18
    Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.


    IF(Nz(ToNumber([Gas Pipe Total Duration])<>0,
    WeekdayAdd([Sub Start Date], ToNumber([Gas Pipe Total Duration])-1))  



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



  • 6.  RE: Null Date Field

    Posted 11-16-2020 10:20
    [Gas Pipe Total Duration] is Formula - Rich Text field. Not sure if that is part of the issue.

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



  • 7.  RE: Null Date Field

    Posted 11-16-2020 10:24
    Ok this doesn't make sense, now when I hover over the highlighted area, it says, "Expecting number/duration/date/timeofday but found bool"

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



  • 8.  RE: Null Date Field

    Posted 11-16-2020 10:26
    Do you have something other than a Rich Text field to use to check if that duration is zero or blank?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 9.  RE: Null Date Field

    Posted 11-16-2020 10:29
    The field type I'm also attempting this formula in is Formula - Date to show date. Not sure if this is an issue either.

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



  • 10.  RE: Null Date Field

    Posted 11-16-2020 11:06
    I'm not sure what you mean by other fields to check for 0? I have an if to display 0 if isnull on a previous field. but it doesn't effect my date formula

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



  • 11.  RE: Null Date Field

    Posted 11-16-2020 11:17
    Is there a way to write If Is 0 then leave blank for the final Date Formula?

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



  • 12.  RE: Null Date Field

    Posted 11-16-2020 11:21
    What is the formula for [Gas Pipe Total Duration] 

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 13.  RE: Null Date Field

    Posted 11-16-2020 11:53
    ToText(Ceil([5. Sub Duration]/8)+Ceil([5. Rough Duration]/8)+Ceil([5. Finish Duration]/8)+Ceil([5. Close Duration]/8) & " " & "Days")

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



  • 14.  RE: Null Date Field

    Posted 11-16-2020 11:58
    I suggest that you make a numeric formula field with the calculation


    Ceil([5. Sub Duration]/8)+Ceil([5. Rough Duration]/8)+Ceil([5. Finish Duration]/8)+Ceil([5. Close Duration]/8

    Then for your field where you want it suffixed with the word "days", hen have that be a separate field.

    The in the formula that you are trying to get working you can refer to a  clean numeric field.

    IF(Nz([Gas Pipe Total Duration NEW numeric field ])<>0,
    WeekdayAdd([Sub Start Date], [Gas Pipe Total Duration NEW numeric field]-1))



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 15.  RE: Null Date Field

    Posted 11-16-2020 12:17
    So how would you recommend getting the days to display?

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



  • 16.  RE: Null Date Field

    Posted 11-16-2020 12:26
    Truthfully I would just call the field with a descriptive name like [xxxx in days] and not try to put the words into the result calculation.

    This field here would be a formula date field


    IF(Nz([Gas Pipe Total Duration NEW numeric field ])<>0,
    WeekdayAdd([Sub Start Date], [Gas Pipe Total Duration NEW numeric field]-1))

    But If you need to also have a formula text field for some purpose which has a suffix of "days", for the duration, then then you can use

    IF(Nz([Gas Pipe Total Duration NEW numeric field ])>0,
    ToText(Nz([Gas Pipe Total Duration NEW numeric field ]) & " days")


     


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 17.  RE: Null Date Field

    Posted 11-16-2020 12:56
    Ok that makes sense. Sorry for the trouble! I'm fairly new to quickbase so I'm still on my learning curve! 

    Thanks for all your help Mark!

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



  • 18.  RE: Null Date Field

    Posted 11-16-2020 12:57
    np, I know at the beginning that you are stumbling around in the dark a bit.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------