Help

Formula to validate several fields

Topic Labels: Formulas
185 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Melissa_Frank1
6 - Interface Innovator
6 - Interface Innovator

I have various contracts and various contractor invoices that I need to review, and I want two formula fields to confirm whether the billing periods covered by each invoice - and the invoiced amount - are "valid". My table is pulling in the contract start and end dates, as well as the budget balance and total invoiced, from another table. 

Right now, I have 3 manual entry fields: 1 for Billing Period Start date, 1 for Billing Period End date, and 1 for Invoiced Amount. I created a formula field to confirm whether the billing period falls between the contract start and end date, but it currently shows billing periods that start or end on one of those dates as invalid. 

IF({Billing Period End}<{PoP End},IF({Billing Period Start}>{PoP Start},"Valid","Outside PoP"),"Outside PoP")

I thought I could adjust the < to <=  and so forth, but figured there must be a more elegant way to do this. 

I also want a field that will confirm whether the contract has sufficient funds to pay the invoice. Linking the invoice to the contract means the Total Invoiced is getting updated with the new invoice amount, so the balance will be updated to show a negative number if they overspent. I'd like to combine that with the dates validation into one formula field, if possible ("Insufficient funds, valid billing period", etc)

2 Replies 2

Don't have any elegant solutions for you, unfortunately. I'd split the "Insufficient Funds" and "Valid Billing Period" into separate formulas and then Concatenate the results.

If your current formula works for you, I'd leave it as it is. You can also try splitting this into an End Date calculation and a Start Date calculation if that helps at all

re: checking whether it's between

This is the script I ended up with the last time I had the same problem:

IF(
  AND(
    OR(
      IS_SAME(
        {Date to check},
        {Start Date},
        'day'
      ),
      IS_AFTER(
        {Date to check},
        {Start Date}
      )
    ),
    OR(
      IS_SAME(
        {Date to check},
        {End Date},
        'day'
      ),
      IS_BEFORE(
        {Date to check},
        {End Date}
      )
    )
  ),
  "{Date} is within start and end dates"
)

Screenshot 2024-07-03 at 11.09.07 AM.png
---

re: I also want a field that will confirm whether the contract has sufficient funds to pay the invoice. 

Hmm, what difficulties did you face setting this up?