Updating date in a worksheet when year is entered

rageshprasad

New Member
Joined
Dec 26, 2012
Messages
40
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.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Data

DE
301/01/2013Sunday

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

Spreadsheet Formulas
CellFormula
E3=TEXT(DAY(D3),"dddd")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

rageshprasad

New Member
Joined
Dec 26, 2012
Messages
40
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,433
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 26, 2012
Messages
40

ADVERTISEMENT

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
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,433
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 26, 2012
Messages
40

ADVERTISEMENT

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.
Tuesday1/1/2013
Wednesday1/2/2013
Thursday1/3/2013
Friday1/4/2013
Saturday1/5/2013 omitted
Sunday1/6/2013
Monday1/7/2013
Tuesday1/8/2013
Wednesday1/9/2013



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


</tbody>
 

rallcorn

Well-known Member
Joined
Nov 11, 2008
Messages
1,027
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,102
Messages
5,640,115
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top