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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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,913
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,913
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,431
Messages
5,837,199
Members
430,484
Latest member
himaruasuka

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