One of my clients is providing loans to customers which repayments should be done daily. If a customer unable to pay more than two days he will have to pay a penalty charge on the next day.
But the company does not open on Sundays. Therefore I want to have a date period in an excel sheet which represent six month period except Sundays. It should be like this.
If I entered the date (date when the loan is provided) in cell B2 Following days should be appeared to in below cells except Sundays. Please see the image.
Can you tell me a formula to get this?
How to create a formula to appear a date?
Your requirement can be fulfill using the functions available in Microsoft Excel. However in the method which I am going to explain, you have to use two columns to get the results.
Open an excel sheet and type the heading as a date in the cell B1. Then leave the Cell B2 to type the start date and type number 1 in cell C3.I typed the start date as 5th December 2012
Then copy down the numbers 1 to more than 180 rows. Please see the image below.
(Here I ask to copy more than 180 rows due to six month period is usually being 180 days)
Now copy the following formula in column B3.
=IF(WEEKDAY(B2+C3,1)=1,B2+C3+1,B2+C3)
Then copy the same formula down to more than 180 rows. Then you are able to see the date range as you expected.
How to create a formula to appear a date?
I'm not good at putting images here but I have a solution for your problem. Let us say that X is the cell where you should input the date. So here's the formula:
=IF(X="","",IF(WEEKDAY(X+1)=1,X+2,X+1))
The formula should be placed under cell X. Then Drag down the cell with the formula to the desired date and then you're good to go. I hope this helps.
