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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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")
 
Upvote 0
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>
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,218,593
Messages
6,143,373
Members
450,483
Latest member
santvik234

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
Back
Top