Counting days between dates with IF conditioning error

Hi!

I have been researching some posts to create a formula that will let me count the days that have gone by since a due date was passed, but are having trouble nailing the formula.
I want to know how much time it took customers to pay their order, the count to stop once the shipment was payed, and also mark in red or with a symbol when their order payment is past expirantion date.

Right now I have these columns:

  • Días crédito (number column; additional days the customer has to fulfill payment; are usually 0, 30 or 60)
  • Vencimiento Pago (due date; formula column adding the date the order was placed + credit days)
    FORMAT_DATE(ADD_DAYS({Fecha Entrega},{Días crédito}),“D MMM YYYY”)
  • Fecha de Pago (date column; date payed)
  • Overdue (formula column with below mentioned formula)

The formula I have now goes like this:
IF({Estado de Pago}=“Pagado”,ROUNDUP(DAYS(TODAY(),{Fecha Pago}),0),ROUNDUP(DAYS(TODAY(),{Vencimiento Pago}),0))

BUT nothing is making sense :smiling_face_with_tear:

As you can see, I have the starting date and then credit days, but the formula is adding days even though some people have 0 credit. If there is a 0, it adds 5; If I input 1, it adds 6, and so on.
Then there’s the overdue column, which has a high number of overdue days even though all of these examples were paid between the time frame.

Can someone please help me understand what I0m doing wrong?

@marcepz

I’m not completely grasping it all. So, let’s take this one step at a time.

  1. The formula in column {Vencimiento Pago} is referring to {Fecha Entrega} not {Fecha Pedido}.
  2. I think the second formula should be: IF({Estado de Pago} = “Pagado”, ROUNDUP(DAYS({Fecha Pago}, {Vencimiento Pago}), 0), “:red_square:” & ROUNDUP(DAYS(TODAY(), {Vencimiento Pago}), 0))

Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Keep your Integromat scenarios from timing out! - YouTube
Contact me directly here: Contact – The Monday Man

Hi @JCorrell!

Thank you so much for the response. The first formula was incorrect :sweat_smile: I’ve corrected it now and it works great.
The second formula is also working!! Right now I am unable to get the red square -for some reason just the text appears. I am trying to find what I’m writing wrong to get it to show, but it’s just that minor inconvenience.

However, I am finding that the people who paid before the expiration date, appear with negative days on the formula. Is there a way to set the formula to start counting until the date of {Vencimiento Pago} is passed or is that too crazy?

@marcepz

I don’t think fixing the negative days will be difficult.

Can you share your current formula?


Jim - The Monday Man (YouTube Channel)
Watch Our Latest Video: Keep your Integromat scenarios from timing out! - YouTube
Contact me directly here: Contact – The Monday Man