Writing this Date/Time exception formula and I need help.

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

If([SLA Expectation] = "Pending part delivery. Need SBD upon delivery.", ToDate([Date Created]), + Days(1)> ToTimeOfDay("9:00 pm"),

IS my formula.
So basiclly I am making an excepting to the SLA Expectiation. If the ticket was made after 9pm I need to be next day. But right now its goes into the next next day. so for example if I make a ticket on the 7/12 at 9pm it will go to 7/14
The whole formula is below. But the part I needs to fix is what i showed earlier. 
If([SLA Expectation] = "SBD", ToDate([Date Created]), If([SLA Expectation] = "Pending part delivery. Need SBD upon delivery.", ToDate([Date Created]), + Days(1)> ToTimeOfDay("9:00 pm"),
ToDate([Date Created])
+ If(ToTimeOfDay([Date Created])> ToTimeOfDay("9:00 pm"), Days(2), Days(1))))
Photo of Charles

Charles

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
I can help, but I'm not understanding your question.

I would like to see the code you had when the formula was working (before your latest change), and then a clear explanation of the change you want to make to it.
Photo of Charles

Charles

  • 0 Points
If([SLA Expectation] = "SBD", ToDate([Date Created]),

ToDate([Date Created])

+ If(ToTimeOfDay([Date Created])> ToTimeOfDay("9:00 pm"), Days(2), Days(1)))



This is the working code I added

If([SLA Expectation] = "Pending part delivery. Need SBD upon delivery.", ToDate([Date Created]), + Days(1)> ToTimeOfDay("9:00 pm"),
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
can you explain in words what this "working code" (that is not working yet) formula is trying to do?
Photo of Charles

Charles

  • 0 Points
So this code works

If([SLA Expectation] = "SBD", ToDate([Date Created]),

ToDate([Date Created])

+ If(ToTimeOfDay([Date Created])> ToTimeOfDay("9:00 pm"), Days(2), Days(1)))

This one does not

If([SLA Expectation] = "SBD", ToDate([Date Created]),
If([SLA Expectation] = "Pending part delivery. Need SBD upon delivery.", ToDate([Date Created]), + Days(1)> ToTimeOfDay("9:00 pm"),

ToDate([Date Created])

+ If(ToTimeOfDay([Date Created])> ToTimeOfDay("9:00 pm"), Days(2), Days(1))))


I am trying to figure out how to write "SLA Expectation = Pending ect.. to work with the rest of the code writen.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
I can't help you if you cannot explain in words what you want your formula to do.  ie in plain English, not code, what change do you want to make to your existing formula that was working.
Photo of Charles

Charles

  • 0 Points
So basically I am making an excepting to the field where the SLA Expectation is Pending Part delivery after 9pm will go to the next day instead of the same day.

For example: I create a ticket for SLA Expectation is Pending parts... 7/12 at 9:01 PM the ticket will be send 7/13 instead of 7/12
But I  only need it to do that for SLA expectations Pending parts Delivery.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
Try this

If(

[SLA Expectation] = "SBD", ToDate([Date Created]),

[SLA Expectation] = "Pending part delivery. Need SBD upon delivery.",


  ToDate([Date Created]) + If(ToTimeOfDay([Date Created])> ToTimeOfDay("9:00 pm"), Days(1), Days(0)),



 ToDate([Date Created])+ If(ToTimeOfDay([Date Created])> ToTimeOfDay("9:00 pm"), Days(2), Days(1)))
Photo of Charles

Charles

  • 0 Points
Yes it worked! Thank you so much!
How do I learn all of this? I am sure you spent years at QB, but I want to learn.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,448 Points 50k badge 2x thumb
You learn by doing  and taking the time to keep at it until it makes sense, and from this forum.  But it can save you time to get some training.  I do run one on one training session over GoToMeeting.  Contact me via the information in my profile if you want to talk and i can show you my typical training agenda topics.

At one time many years ago there was a book called the Missing Manual, sort of a Dummies style book which was excellent, but with the QuickBase features changing so quickly, that has never been updated.  There are seminars offered on a range of topics http://quickbase.intuit.com/webinars and that page has a link to recorded webinars. http://quickbase.intuit.com/webinars#recorded

But while there is a cost, the fastest way up the learning curve is one on one training.