How to create a formula to appear a date?

Asked By 30 points N/A Posted on -
qa-featured

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.

Setting Formula for Dates

Can you tell me a formula to get this?

SHARE
Best Answer by Geisha Nitta
Best Answer
Best Answer
Answered By 0 points N/A #134118

How to create a formula to appear a date?

qa-featured

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)

In excel type heading

Now copy the following formula in column B3.

=IF(WEEKDAY(B2+C3,1)=1,B2+C3+1,B2+C3)

Formula for Date

Then copy the same formula down to more than 180 rows. Then you are able to see the date range as you expected.

Copy formula to all rows
Answered By 0 points N/A #134119

How to create a formula to appear a date?

qa-featured

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.

Login/Register to Answer

Related Questions