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

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.

- The formula in column {Vencimiento Pago} is referring to {Fecha Entrega} not {Fecha Pedido}.
- I think the second formula should be: IF({Estado de Pago} = “Pagado”, ROUNDUP(DAYS({Fecha Pago}, {Vencimiento Pago}), 0), “” & 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 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