Linking data from master sheet into monthly date tabs

%3CLINGO-SUB%20id%3D%22lingo-sub-2777506%22%20slang%3D%22en-US%22%3ELinking%20data%20from%20master%20sheet%20into%20monthly%20date%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2777506%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20excel%20sheet%20with%20a%20mixture%20of%20dates%20and%20text%20and%20trying%20to%20link%20the%20dates%20into%20month%20tabs%20from%20January%20to%20December%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2777506%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2777795%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20data%20from%20master%20sheet%20into%20monthly%20date%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2777795%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164303%22%20target%3D%22_blank%22%3E%40Karen281910%3C%2FA%3E%26nbsp%3BThe%20first%20step%20you%20need%20to%20take%20is%20to%20make%20sure%20Excel%20recognizes%20each%20date%20as%20a%20true%20date.%20Any%20%22date%22%20that%20is%20left-aligned%2C%20isn't%20recognized%20as%20a%20date%20by%20Excel.%20Currently%20only%20about%20290%20of%20the%20almost%201600%20cells%20in%20the%20Date%20columns%20contain%20a%20valid%20date.%3CBR%20%2F%3EIf%20your%20data%20would%20have%20been%20structured%20like%20this%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222021-09-23_11-49-35.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312370i2EB325026E99E927%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222021-09-23_11-49-35.png%22%20alt%3D%222021-09-23_11-49-35.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20would%20have%20been%20far%2C%20far%20easier%20to%20setup%20a%20page%20on%20which%20you%20can%20simply%20select%20a%20period%20to%20see%20all%20activity%20in%20that%20period.%20Or%20to%20just%20filter%20the%20above%20table%20on%20the%20(single!)%20date%20column%20and%20on%20the%20Building%20column%20for%20example.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2777831%22%20slang%3D%22en-US%22%3ERe%3A%20Linking%20data%20from%20master%20sheet%20into%20monthly%20date%20tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2777831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20reply%2C%20out%20of%20interest%20how%20did%20you%20format%20the%20structure%20in%20the%20example%20given%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a excel sheet with a mixture of dates and text and trying to link the dates into month tabs from January to December

6 Replies

@Karen281910 The first step you need to take is to make sure Excel recognizes each date as a true date. Any "date" that is left-aligned, isn't recognized as a date by Excel. Currently only about 290 of the almost 1600 cells in the Date columns contain a valid date.
If your data would have been structured like this:

2021-09-23_11-49-35.png

it would have been far, far easier to setup a page on which you can simply select a period to see all activity in that period. Or to just filter the above table on the (single!) date column and on the Building column for example.

 

@Jan Karel Pieterse 

Thank you for reply, out of interest how did you format the structure in the example given?

 

I manually copied and pasted (Paste special, transpose option) some cells of your table to create the sample table.

@Karen281910 Attached is a file where I used Data, Get Data to transform the table into the structure I would suggest to use

Thank you for the attached. How would I be able to link this to monthly tabs, so if I wanted to select a tab for January this would show me the service dates due in January?
Well, you do not necessarily need a Jan tab, you could just filter on the date column and choose Jan as the month