Solved Excel, Year display problem

February 17, 2020 at 22:41:05
Specs: Windows 10
Need Excel help in here guys, tried everything (with my limited knowledge) and nothing seems to be working. I also looked for answers in this Office section and I couldn't find anything that could solve my problem. I know you guys will have the answer, no manual or guide can beat your know-how of Excel or Word, that is something I learned by occasional visit to this place over the last 10 years.

Firstly, Small info >> I made a basic Calendar in Excel and it's a business weekly type, week below the week continuously all the way to the end of the year. One simple formula is typed in all Calendar date cells. You can see in example below the setup (first 2 rows should do).

(Column A is reserved for displaying Months, formated as TEXT, each month typed in vertical text, bottom to top)
1st row >> B1 to H1 (this row of cells is grouped into one, formated as TEXT and I manually type the YEAR digits here)
2nd row >> B2 (this cell is blank), C2=B2+1, D2=C2+1, E2=D2+1, F2=E2+1, G2=F2+1, H2=G2+1

Row #2 is the first week. All week rows to the end of the Calendar are formated to CUSTOM (just a single d typed in bracket). When I type the date 1-1-21 (which is 1st of Jan 2021, Friday) in cell F2, all dates for each month just spread down from here on all the way to the end of the Calendar. Then I simply clear the contents in cells prior to 1st or Jan and in cells after the 31st of Dec. Job's done! Excel knows if February needs 28 or 29 days depending on what Date-Year I type in that first cell at the beggining of the year (it knows the leap years). This setup works perfectly.

Ok, The Problem >> I would like the year digits 2021 to automatically appear in B1 to H1 cell once I type the first date 1-1-21 in F2. Ok, I know I could simply format B1 to CUSTOM (type yyyy in bracket), type function =F2 in it and job's done, but, I would have to do that manually every time 1st of Jan moves to a different cell in that second row (depending on what Date-Year I start with). Because the 1st of Jan is constantly changing spots year after year (only in the 2nd row) I am clueless as to how to make a usefull formula that will automatically display year digits in B1. Please help with some formula here, and please tell how should I format that B1 cell.

Best regards from Australia, Robbie


See More: Excel, Year display problem


#1
February 18, 2020 at 04:06:09
✔ Best Answer
Maybe I'm missing something, but it seems to me that H2 will always contain the year that you are interested in, whether you type in 1/1/xxxx in H2 or the formula calculates it.

Why not use H2 as your reference cell to provide the year in B1?

Further, if you are clearing the cells prior to 1/1/xxxx doesn't that mean that you have to re-enter some Row 2 formulas every now and then? Just trying to make sure I understand your process.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
February 18, 2020 at 05:07:30
Wow man, haha. Yes of course, the H2 will always be the wanted year and I will definitely use that as a reference now. So simple yet it just never occurred to me. I've been cracking my head over this one for the past 2 days and I never thought of this simple solution. You're a genius DerbyDad, thank you so much. This is a solved and shut case.

In regards to your mention of me having to re-enter some cells in Row2 or perhaps the LastRow in Calendar - well that's an easy one. I don't have to re-enter anything, I made a template with all cells filled up (except cell #1). When I'm done with one year, I save it as a new file, then the Template comes in again with all cells filled up. I enter the 1st of Jan, delete cells before it and after the 31st Dec, save this as new file. Repeating this process over and over, each file is saved as Cal-2010, Cal-2011, Cal-2012 and so on. The template file is always in its original condition. I made this calendar in 2 sections, 6 months in each upright section. Jan to Jun and Jul to Dec in second section, Mon to Sun format in each section. These 2 sections have little spacing between them of 25pix, and both perfectly fitting in A4 sheet, ready for printing. Nice looking calendar and simple (regarding the formulas I used in it). I place a thicker line border around each month and I didn't worry about Mon to Sun text display above the Cal. It should be obvious to anyone in what column is Tue, or Sat just by looking at it, and also, I'm the only one using this calendar, I know what's going on here.

Thank you again, Sir!

message edited by Robbie777


Report •
Related Solutions


Ask Question