Discussions

Expand all | Collapse all

Null Date Field

  • 1.  Null Date Field

    Posted 16 days ago
    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 15 days ago
    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 13 days ago
    Hey Mark, I'm getting an error that is saying 'expecting bool, but found number'

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



  • 4.  RE: Null Date Field

    Posted 13 days ago
    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 13 days ago
    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 13 days ago
    [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 13 days ago
    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 13 days ago
    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 13 days ago
    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 13 days ago
    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 13 days ago
    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 13 days ago
    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 13 days ago
    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 13 days ago
    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 13 days ago
    So how would you recommend getting the days to display?

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



  • 16.  RE: Null Date Field

    Posted 13 days ago
    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 13 days ago
    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 13 days ago
    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
    ------------------------------