View original (French)

Late Days Calculation Formula.

VASE -  
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   -
Hello,

I am using the following formula to calculate the number of overdue days:

=DATEDIF(A5,TODAY(),"d")

However, for the days that are not yet overdue, I receive an error message.

What would be the correct formula?

Best regards.

Configuration: Windows / Edge 17.17134

17 réponses

Réponse 1 / 17
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Hello,

I didn’t understand everything but try with a conditional

=IF(A5>=TODAY(),"",DATEDIF(A5,TODAY(),"d"))

--
See you +
Mike-31

I am responsible for what I say, not for what you understand...
1
Réponse 2 / 17
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
1) Go to https://www.cjoint.com/
2) Click on [Browse] to select your file (15 MB max)
3) Scroll down to click on the blue button [Create the Cjoint link]
4) After a few seconds, the second page displays, with the link in bold; right-click on it and choose "Copy link"
5) Go back to your discussion on CCM, and in your message, press "Paste".
=>See the document https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers

In the same style, you have https://mon-partage.fr/

--
A+
Mike-31

I am responsible for what I say, not for what you understand...
1
Réponse 3 / 17
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
If you place Mike's formula in K, it shows 13 in K3 and 6 in K4

provided, of course, that since we start in line 3, you put the correct row number in K3

=IF(A3>=TODAY();"";DATEDIF(A3;TODAY();"d"))

but still complete it as follows if you don't want to have, when A is empty, more than 43,500 days of delay
=IF(A3="";"";IF(A3>=TODAY();"";DATEDIF(A3;TODAY();"d")))

kind regards
1
Réponse 4 / 17
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Re,

so in H, let this formula be
=IFERROR(ROUND((((((3.73*3)/365)*G3)*(B3+C3))/100),2),"")
or
=IF(G3="","",ROUND((((((3.73*3)/365)*G3)*(B3+C3))/100),2))
and the same in I
=IF(G3="","",B3+C3-D3+E3+H3)
and in J
=IF(G3="","",J2-D3-E3+H3)
A+
Mike-31

I am responsible for what I say, not for what you understand...
1
VASE
 
It's fine, just that if there are no interests, column I should take the sum of B+C.
0
VASE
 
And column J, the remaining balance to be paid.
0
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
What do you mean, in column J you want the balance of J2 to be displayed if there is nothing in G, H, and I?
0
VASE
 


J2 = 439.75

J3 = 439.85
J4 = 439.90

J5 = 439.90
J6 = 439.90
J7 = 439.90
...
0
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Hi,

in J3, paste this formula and drag it down to see if it's what you're looking for

=IF(A3="","",IF(AND(A3<>"",G3=""),J2,J2-D3-E3+H3))
0
Réponse 5 / 17
VASE
 
With this formula, nothing is displayed anymore.
0
Réponse 6 / 17
VASE
 
DATE OF PAYMENT AMOUNT TRANSFER AMOUNT CESU TRANSFER RECEIVED CESU RECEIVED TOTAL RECEIVED DAYS LATE LATE INTEREST BALANCE DATE GLOBAL BALANCE
439.75 €
May 28, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € 13 0.10 € 25.11 € 439.85 €
June 4, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € 6 0.05 € 25.06 € 439.90 €
June 11, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
June 18, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
June 25, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
July 2, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
July 9, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
July 16, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
July 23, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
July 30, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
August 6, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
August 13, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
August 20, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
August 27, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
September 3, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
September 10, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
September 17, 2018 17.01 € 8.00 € 0.00 € 0.00 € 0.00 € #VALUE! #VALUE! #VALUE! #VALUE!
0
Réponse 7 / 17
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Hello
you should check that in column A your dates are not text, Michel's suggestion is correct
try column A:
cell format "standard"
the dates should display as a 5-digit number
regards

--
The quality of the response mainly depends on the clarity of the question, thank you!
0
Réponse 8 / 17
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Hi,

according to your table, I understood correctly and my formula works perfectly; there must certainly be a formatting issue.

Instead of a screenshot, create an example of an anonymized Excel file and attach it to the discussion.

--
See you,
Mike-31

I am responsible for what I say, not for what you understand...
0
Réponse 9 / 17
VASE
 
How do I attach my file?
0
Réponse 10 / 17
VASE
 
Je suis désolé, mais je ne peux pas accéder à des liens ou à des contenus externes. Veuillez fournir le texte à traduire directement ici.
0
Réponse 11 / 17
VASE
 
I no longer have any errors in column G, but now columns H, I, and J are in error...
0
Réponse 12 / 17
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Re
the use of volunteers here requires a minimum of goodwill from the requesters, don’t you think?
your columns may be incorrect, but the file you submitted does not contain any formulas after G
So let's not play guessing games!
Have a good evening
sincerely

--
The quality of the response mainly depends on the clarity of the question, thank you!
0
Réponse 13 / 17
VASE
 
Sorry, I didn't play the guessing game, but to send you the spreadsheet, I copied and pasted and the formulas disappeared.

I'll fix it and resend.
0
Réponse 14 / 17
VASE
 
I hope everything is in the file now...
0
Réponse 15 / 17
Mike-31 Posted messages 18405 Registration date   Status Contributeur Last intervention   5 145
 
Re,

As my friend Vaucluse rightly says, we need to know how you calculate your interests in H3, the balance in I3
For column J, if the overall balance is the value in J2 of €439.75 + the interests in column H, the formula would be
=$J$2+H3 but your value in J4 cannot be €439.90 but €439.80 because €439.75 + €0.05

If you want us to help you, you need to provide a minimum of explanation and involvement from your side

--
A+
Mike-31

I am responsible for what I say, not for what you understand...
0
Réponse 16 / 17
VASE
 
J2 is the original amount of the invoice. (Balance to be paid initially)
H3 is the amount of interest calculated on the sum of B3+C3 * the number of days and the interest rate.

I3 is the sum of B3+C3+H3 minus D3 and E3

J3 is the new balance remaining to be paid, calculated as J2 minus D3 and E3 plus H3.
0
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
voir message au dessous
0
Réponse 17 / 17
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
Here is what works better in the last file

Regardless of the calculation, it returns #VALUE when G is equal to "" (due to the formula for calculating late days)
It should suffice for all the formulas that use G, starting at H3:

=IF(G3="","",ROUND((((((3.73*3)/365)*G3)*(B3+C3))/100),2))

Or shorter, Excel from 2007 onwards:
=IFERROR(ROUND((((((3.73*3)/365)*G3)*(B3+C3))/100),2),"")

Principle to be applied to columns I and J as well

Best regards

--
The quality of the answer mainly depends on the clarity of the question, thank you!
0