Excel Workout 17 - If Function!

Welcome to Excel Workout #17!
Difficulty Level:

This week’s challenge is designed to test your knowledge on If Function.

If Function
The IF function in Excel is a logical function that allows you to perform different calculations or actions based on a specified condition. It evaluates a condition and returns one value if the condition is true, and another value if the condition is false.

Please follow the directions given below, which include downloading the Excel worksheet required to perform the challenge tasks. Once you have completed the download, proceed to take the challenge and test your skills.


  • In column E cells, determine the number of days by
    which the payment exceeds the due date. A payment is
    classified as overdue if it is paid more than 4 days after
    the date of receipt.

  • In column F cells, compute the penalty amount - which is
    equal to 3% of the order amount - for every day the
    payment is delayed.

  • In column I cells, calculate the total number of days the
    payment is overdue and the corresponding fine amount
    based on the table. Compare the calculated results with
    the corresponding values in column K.

Reply to this post with your formula code and solution file. Please be sure to blur or hide your formula code.

This workout will be released on Monday May 22, 2023 , and the author’s solution will be posted on Sunday May 28, 2023 .

Challenge #17.xlsx (8.3 MB)

Good luck,

Ilgar Zarbaliyev

1 Like

This is quite straight forward

Due Date= Receipt Date + 4

Due Date= C2 + 4

Overdue Days=
IF( Payment_Date>Due_Date, Payment_Date-Due_Date,0)



=IF(D2-(C2+4)<0,0, D2-(C2+4))

Penalty= =3% * Order_amount * Overdue_days

=3% * B2 * E2

Quadri Atharu Challenge #17.xlsx (8.3 MB)

1 Like

Hello Quadri,

Thank you for your solution.
Different approach.

Overdue days:


Challenge #17-TuralMammadov.xlsx (8.3 MB)

1 Like

Here is my file

Challenge #17.xlsx (1.7 MB)

1 Like

Challenge#17.xlsx (8.3 MB)

Overdue (days)


Penalty, $


Using Days function is great.
Thank you for your solution.

1 Like

Hi @IlgarZarbaliyev,

Here is my solution to the workout #17.

OverDue (Days)

= if((Receipt Date +4)< Payment Date, (((Receipt Date +4)-Payment Date) *-1), 0)

NOte Multiply by -1 make the days positive number)


Penalty $
=If(Overdue days > 0, (Round((.03*Amount $ * Overdue(days),2), 0)
Round to 2 decimals

File uploaded:
Excel Workout Challenge #17 Keith Smith.xlsx (8.3 MB)

thanks for the workout

1 Like

=IF(DAYS($D2,$C2)-4 >= 1,DAYS($D2,$C2)-4,0)
=($B2* 0.03*$E2)
[Munachi_Challenge #17.xlsx|attachment](upload://58EJPtcgMR57Wrgz0wBnlcpoiPV.xlsx) (8.3 MB)

1 Like

Here is my submission.

Challenge #17.xlsx (8.3 MB)


Calculating Overdue Days in E2:

=IF(D2-C2>4, D2-C2,0)

Calculating Penalty Amt. ($) in F2

=IF(E2>0, B2*3%*E2,"")

Total Overdue Days:


Total Penalty ($):


Challenge#17_Homesh Agarwal.xlsb (19.7 KB)


Thank you for participating in the Excel Challenge related to IF Function! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to work with IF Function in Excel.

Here is my solution to Workout #17:

  • To find Overdue (days):

  • To find Penalty, $:

Challenge #17 with Solution.xlsx (8.3 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

1 Like
  1. Overdue(days) calculation formula logic:
    IF Payment Date > (Receipt Date + 4)
    THEN Payment Date - (Receipt Date + 4)
    ELSE 0


  1. Penalty,$ calculation formula logic:
    IF Overdue(days) IS NOT ZERO
    THEN 3% * Amount * Overdue(days)
    ELSE consider the penalty as zero


  1. Total Overdue(days) [column I] calculation:
    SUM of all positives in the Overdue(days) [column E]


  1. Total Penalty,$ [column I] calculation:
    SUM of all penalty, $ in the [column F]

= SUM(E2:E35)

My Excel Workout File: Challenge #17.xlsx (8.3 MB)

1 Like


Code used in cell E is =IF(D2>C2+4, D2-(C2+4), “Not Overdue”)
Code used in Cell F is =IFERROR(((B3*3%)*E3), “No Penalty”)


Copy of Challenge #17.xlsx (8.3 MB)

1 Like

Challenge #17 -Steve Waring.xlsx (8.3 MB)