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