How to create a formula to appear a date?

Asked By Sandra Teylor 30 points N/A Posted on - 07/29/2012

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?

• Status: Open
• Question Views: 982
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Geisha Nitta

Go To Solution

Best Answer

Best Answer

Answered By Geisha Nitta 0 points N/A #134118

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.

About Geisha Nitta

Questions
1

Answers
55

Best Answers
27

• Vote Up 0 Vote Down

• Posted on - 07/29/2012
• Question Category: MS Excel

Answered By Anthew Staw 0 points N/A #134119

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.

About Anthew Staw

Questions
3

Answers
34

Best Answers
3

• Vote Up 0 Vote Down

• Posted on - 08/02/2012
• Question Category: MS Excel