# Discussions

View Only

## 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
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
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
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
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
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
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