Updating date in a worksheet when year is entered

[rageshprasad]

Hello All,

I have a finacial template in excel. Every year this files needs to update cell by cell for each day in respective to each date.
e.g if 2013 1st Jan is Tuesday the 2014 1st Jan will Wednesday.

Is there a way that we just enter the year in a cell and the entire work book changes for each date to refelect the correct day.

Thank you.

 

[Dryver14]

Data

	D	E
3	01/01/2013	Sunday

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 115px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E3 =TEXT(DAY(D3),"dddd") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[rageshprasad]

That make complete sense, but i have 1000 records of data in the template. It has dates and refected days. I want to be able to automate the sheet where in one cell i enter the year the entire work sheet will change to the correct date and day.

 

[JoeMo]

Maybe just define a named range (single cell) somewhere in the workbook called, for example, CurrYear, where you can make a single entry for the year of interest (e.g 2013).

Example: Cell A1 on Sheet1 is named CurrYear and is formatted as a number.

In Sheet2 cell D3 has the formula:
=DATE(CurrYear,1,1)
and E3 has the formula:
=TEXT(DAY(D3),"dddd")
Both cells will change whenever a new entry is made to Sheet1 cell A1.

 

[rageshprasad]

Maybe just define a named range (single cell) somewhere in the workbook called, for example, CurrYear, where you can make a single entry for the year of interest (e.g 2013).

Example: Cell A1 on Sheet1 is named CurrYear and is formatted as a number.

In Sheet2 cell D3 has the formula:
=DATE(CurrYear,1,1)
and E3 has the formula:
=TEXT(DAY(D3),"dddd")
Both cells will change whenever a new entry is made to Sheet1 cell A1.

When i enter the Date function it displays "1/1/2013" and that is correct.
when i entered the Text Function it displays "Sunday" but actually it should be Tuesday.

Any more suggestions.

Thanks

 

[JoeMo]

When i enter the Date function it displays "1/1/2013" and that is correct.
when i entered the Text Function it displays "Sunday" but actually it should be Tuesday.

Any more suggestions.

Thanks

Sorry, the formula in E3 should be:
=TEXT(WEEKDAY(D3,1),"dddd")

 

[rageshprasad]

Sorry, the formula in E3 should be:
=TEXT(WEEKDAY(D3,1),"dddd")

Thank you so much "JoeMo" you are the best.

Now that this is working - I'm exploring more options. Since this worksheet will have work days (Monday to Friday for the entire year) as shown in the example below.
My challenge to make this template is this. When a date is entered from 1/1/2013 so forth, how do you control the date since they are consecutive and you want to omit the weekends. (omit saturday and sunday).
Any suggestion would be appreciated. Thanks.

Tuesday	1/1/2013
Wednesday	1/2/2013
Thursday	1/3/2013
Friday	1/4/2013
Saturday	1/5/2013 omitted
Sunday	1/6/2013
Monday	1/7/2013
Tuesday	1/8/2013
Wednesday	1/9/2013

<colgroup><col style="width: 48pt;" span="2" width="64">
<tbody>


</tbody>

 

[rageshprasad]

Data

	D	E
3	01/01/2013	Sunday

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E3 =TEXT(DAY(D3),"dddd") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks, your solution was also helpful.

 

[rallcorn]

Try:

D3 formula to always return the first WEEKDAY
=IF(WEEKDAY(DATE(CurrYear,1,1),2)=6,DATE(CurrYear,1,3),IF(WEEKDAY(DATE(CurrYear,1,1),2)=7,DATE(CurrYear,1,2),DATE(CurrYear,1,1)))

E3 (copied down) formula to always return the date of the next WEEKDAY
=IF(WEEKDAY(D3+1,2)<=5,D3+1,D3+3)