Formula to validate several fields

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)

Page 1 / 1

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"
)

---

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?

Thanks for your help! I ended up settling for one formula just showing me if the date is between the contract start and end dates:

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

Then I just have a lookup in the Invoices table that shows the Balance, and I am crossing my fingers my team will notice a negative number there. I wanted an "OVER BUDGET" angry red flag to popup but that's honestly more likely to be caught just in normal review than the invoice date being outside the contract dates, so I think we will be OK.

Oh I forgot to include that I ultimately also need to see for each contract how many months they've invoiced for and the average amount invoiced per month. I could do some kind of roll-up getting the earliest date from "Billing Period Start" then a second one for the latest date from "Billing Period End", right? Then some formula for calculating the number of months between those dates etc etc.

Yeap that sounds like it'd work!

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded