Auto dates in excel

[Apf]

Hello,

I'm wondering if someone can help with filling dates in excel,

I have a spreadsheet that has a week per sheet, the dates of which in each sheet is displayed several rows apart, is there a way to have the dates auto fill, both within the sheet and if I was to duplicate the sheet for the following week?

Many thanks in advance

 

[Lee J]

Hi Apf,

If you use the date as part of the sheet name you can use the formula below:

=RIGHT((CELL("Filename",A1)),10)

So if your sheet is called 'Week Commencing 24/07/2020' the above formula will pull 24/07/2020 (10 characters from the right) into the cell with that formula in then simply reference that same cell and add a 1 to it for 25/07/2020 and so on.

Hope I understood your requirement

 

[arthurbr]

Hi and welcome
You seem to be making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form /workbook that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyze and summarize or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

 

[Apf]

Hi Apf,

If you use the date as part of the sheet name you can use the formula below:

=RIGHT((CELL("Filename",A1)),10)

So if your sheet is called 'Week Commencing 24/07/2020' the above formula will pull 24/07/2020 (10 characters from the right) into the cell with that formula in then simply reference that same cell and add a 1 to it for 25/07/2020 and so on.

Hope I understood your requirement

Thanks for the reply! I will have a play around with this later,

Any idea of auto filling the dates that are several merged rows apart?

Regards

 

[Apf]

Hi and welcome
You seem to be making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form /workbook that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyze and summarize or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

Not something I have created but forced to use with the formatting, but thanks for the lengthy feedback

 

[Lee J]

Hi Apf,

If you could provide a cutdown version of the file I could take a look its difficult to provide a solution without seeing the issue

 

[Apf]

Hi Apf,

If you could provide a cutdown version of the file I could take a look its difficult to provide a solution without seeing the issue

The following image is what I have, but the whole week and each week in a different sheet, is auto dating possible?

Thanks

 

[Lee J]

Hi Apt,

Sorry I'm now without access to Excel until after this weekend but will look Monday first thing

 

[Lee J]

Hi Apt,

Ok so I have a solution

Firstly you will have to add a macro as a module with this code inside:

Function ISMCell(rCell As Range) As Boolean
' Returns true if referenced cell is merged
ISMCell = rCell.MergeCells
End Function

Then in the cell of the column you want the date to appear put the below formula adjusting the cell references to suit your sheet:

=IF(ISMERGED(A2),LOOKUP(2,1/($B$1:$B1=""),$A$1:$A1),"")

I've used column B and what the above does is check to see if A2 is a merged cell if not it marks B2 as "" (empty) cell alternatively if A2 is merged it finds the last "" (empty) cell and returns the value from column A.

Note from the first image below that B1 has been left without a formula so it counts as the first "" (empty) cell yet B23 has a formula but gets left out and the formula in B25 picks the date up from the formula in B24 being the last "" (empty) cell therefore giving the 27/07/2020 from A24.





Not sure if you know but these can be clicked to open in a new window full size

 

[Apf]

thanks for getting back i really appreciate it!

i feel like maybe i havnt explained myself properly,

ive used the below as a better example, the table above being in one sheet, the table below being in a second, i would like to be able to auto date the rest of the second table with the same format as the table above it.

and then im wondering if this can be auto dated on the next sheet? with the sheet name matching the first date in each table?