Matching a date on a worksheet tab to a date of a record

[dpaton05]

I have a table that has records within excel. The record has a date in the format dd/mm/yyyy. The table will be used as a data entry form with the record being copied and pasted into the relevant tab for the month. The name of the worksheets are July2018, August2018 etc. I have added columns to the table that extract the year and month from the date field by formatting the new columns to show only the month or the year. I was thinking I could use the concaternate function to add the two fields together and then somehow paste the record into the appropriate worksheet.

Using concaternate, it doesn't add the fields together to give me month/year, it only gives me a number.

I am a bit lost how to put the record into the appropriate month/year worksheet depending on the date of the record.

Sorry if I have already posted a question like this, I couldn't remember if I did or not.

 

[Kamolga]

I am not sure to understand the question but if you want dates to look like July2018, select them, right click and go to format cells, then custom and type

mmmmyyy

 

[dpaton05]

Sorry if I was to confusing in my explanation. I just need a way to identify the sheet to store a record in based on the date of the record. The sheets are named by for instance, august2018 and the record has a date field of dd/mm/yyyy. The records for august 2018 need to be stored on the august 2018 sheet. So a record is entered on the data entry sheet and then filtered to the correct sheet. I have worked out how to copy the needed parts of the record to the new sheet but I can't work out how to find the correct sheet to put it in.

I hope I was not as confusing this time.

 

[Fluff]

If you simply want a cell that shows the monthYear try

Excel 2013/2016
	D	E
3	02/10/2018	October2018
Sheet1

Cell Formulas
Range	Formula
E3	=TEXT(D3,"mmmmyyyy")

 

[Kamolga]

But how will you get the record from data to the month sheet, through macro or formula? To meet 1 page per month I would personnally chose pivots if file is not too big, which would update when activating sheet (macro)...or records copied through a macro if a lot of data. But honestly I would avoid one page per month and instead I would have 1 sheet called "monthly view" with one pivot table and a slicer to chose the months

Now, if in the Sheet January2018 you enter

=Right(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4)

, it will show

2018

.

If in the Sheet August2018 you enter

=LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),LEN(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))-4)

it will show

August

If in the Sheet August2018 you enter

=MONTH(DATEVALUE(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),LEN(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))-4)&" 1"))

, it will show month number

8

If in the Sheet August2018 you enter

=DATE(RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),4),MONTH(DATEVALUE(LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),LEN(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255))-4)&" 1")),1)

, it will show

1/8/2018

 

[dpaton05]

Given that information, what code would allow me to filter the worksheet based on the date of the record and match that to the name of the name of the sheet?

How could it be done with pivot tables, what are they and what do they do?