Create calendar with Excel formula and conditional formatting Part 2

It is 2023 and I made an improved version of the Excel Calendar with a neat design and highlight the holidays.

Day of Week Header

Firstly, input the year in cell $B$1 and create the day of week header. The day of week header only apply in row 2 and we use ROW() to ensure this.

In cell B1, type the below formula and copy to B1:H1

=IF(ROW()=2,TEXT(COLUMN(),"ddd"))

Day of the year

To construct the calendar, we fill up the cells with the day of the year (0-365). We need to offset by WEEKDAY(DATE($B$1, 1, 1) so day 0 fit in the correct day of week. It may not be easily noticed ince 1-Jan of 2023 is Sunday. It will be more clear if change the year to 2022.

=IF(ROW()=2,TEXT(COLUMN(),"ddd"),(ROW()-3)*7+COLUMN()-WEEKDAY(DATE($B$1,1,1)))

Date Value

Now we add the first day of the year to to make it a date value so we can use conditional formatting to format it. Here we use TEXT() to format the date for verification only. This will be removed in actual formula.

=IF(ROW()=2,TEXT(COLUMN(),"ddd"),TEXT(DATE($B$1,1,1)+(ROW()-3)*7+COLUMN()-WEEKDAY(DATE($B$1,1,1)),"yy-mm-dd"))

Hide the date that does not match the input year.

=IF(ROW()=2,TEXT(COLUMN(),"ddd"),IF(YEAR(DATE($B$1,1,1)+(ROW()-3)*7+COLUMN()-WEEKDAY(DATE($B$1,1,1)))=$B$1,DATE($B$1,1,1)+(ROW()-3)*7+COLUMN()-WEEKDAY(DATE($B$1,1,1)),""))

Fill up the worksheet

Copy the formula to cell A2:G55 to fill up the whole year

Format the calendar with Conditional Formatting

We then apply conditional formatting to format the calendar. Since the cell value is already a Date. We can use the date format to show the month name or day of month number.

Select column A:G and apply below Conditional Formatting:

Show month name

Use custom format mmm to show the month name in the first day of the month

Formula:

=AND(ROW()>2,DAY(A1)=1)

Format:

Number -> Custom -> "mmm"

Day number

Use custom format d to show the day number of remaining days of the month

Formula:

=AND(ROW()>2,DAY(A1)>1)

Format:

Number -> Custom -> "d"

Border of month

Add a bottom border if reach end of the month (i.e. the day of month of next row is smaller than current cell

Formula

=IF(ROW()<=2,TRUE,IF(A1="",A2<>"",IF(A2="",TRUE,DAY(A1)>DAY(A2))))

Format:

Border -> Bottom border

Add a right border for the last day of the month (i.e. the cell in the right is "1")

Formula

=AND(ROW()>2,IF(AND(COLUMN()<7,B1=""),A1<>"",DAY(B1)=1))

Format

Border -> Right border

Holiday

We highlight Saturday and Sunday in red. Also we can input the holiday and use Vlookup to mark the day as holiday as well.

Formula

=AND(ROW()>1,A1<>"",OR(COLUMN()=1,COLUMN()=7,NOT(ISNA(VLOOKUP(A1,$J:$J,1,FALSE)))))

Format

Font -> Color -> Red

Protect Sheet

In last step, we unlock the cell $B$1 for input the year, and protect the worksheet

Revisit Microsoft Style Guide in 2022

In 2009 I blogged about Microsoft Manual of Style (now is called Microsoft Style Guide) and list some interesting terms.  At that time I were referring to the 3rd edition which was published in 2004.  Today at 2022, let's revisit the list to see what terms are added, what are removed to have a quick review on how computing technology changes in past 18 years.

New:

• New technology (AI, Bluetooth, Blu-ray Disc, big data)
• Social Media (chatbot, emoji, instant message, invite, profile)
• Cloud related
• Mobile related (touch-, wearable, mixed reality, notification, rotate, swipe, tap)
• Gaming (achievement, badge, e-sports)
• Security (honeypot, malicious-, malware, multifactor authentication,  spyware, trojan)

Removed:

• Legacy technology (CD (burn, rip), COM port, DOS, modem, diskette, push-to-talk, PCMCIA, VCR, VGA)
• Low level computing (assembly language, DWORD, interrupt, linefeed, machine language)
• Obseleted Microsoft products /technologies (COM, DOS, Front Page, NT, OLE, Winsock)
• Early internet terms (webzine, net-, newsgroup, sitemap, Usenet, Weblication)
• Pre-cloud (scale-up, virtual)
• Intel (IA32, Itanium, Intel-based, x86)

Details:

New	Removed
achievement (gaming) active player  AI antimalware antispyware app audiobook	access privileges action bar alt text (html) assembly language audit trail
badge (gaming) big data bio Bluetooth Blu-ray Disc	backward bitmask bitplane bitwise black hole (networking) bulleted burn (CD)
cell phone, cellular phone cellular center on chatbot check in cloud cloud platform cloud services community cloud company vs. organization compute conversation as a platform (CaaP) conversational user interface (CUI) -core cyber	caller ID card carrage return/line feed (CR/LF) CBT (computer-based training, now is called e-learning) CD, CD case, CD key, CD Plus certificate channel client area (now is desktop or workspace) code signing COM (Component Object Model) communication port (COM port) comprise critical process monitor critical section object cursor
dark mode deceptive software DevOps dynamic service capacity	data modem diskette Distributed File System DOS double word (DWORD) downlevel
e-discovery elastic service capacity e-learning emoticons, emoji End-User License Agreement (EULA) e-sports	Editor e-zine, webzine
family fitness band flick flyout freeze, frozen	facsimile firewall Fortran Front Page
General Data Protection Regulation (GDPR)	Gantt chart gateway
hang hard-of-hearing HDMI hierarchical menu high-level-language compiler honeypot hybrid cloud	hardware high-level highlight hotfix
infrastructure as a service (IaaS) instant message, IM Internet of Things (IoT) invite IT pro, IT professional	IA-32-based I-beam independent content provider (ICP) insertion point Intel-based Interrupt inverse video Itanium-based (Intel CPU architecture)
	jwewl case
	kludge
leading license terms	line feed localhost look up low-level
machine learning malicious code, malicious user malware, malicious software mission critical mixed reality multifactor authentication	MAC (media access control) machine language Macro Assembler (MASM) memory models movement keys MS-DOS My Computer
natural user interface, NUI .NET newsreader noncontinguous selection notification	NET (net-) new line newsgroup nonadjacent selection non-Windows application NT
off-premises, on-premises onboarding online services open source, open-source opt in, opt out org, organization	Object Linking and Embedding (OLE) online Help on/off switch overtype
page pan passwordless pin, unpin pinch platform as a service (PaaS) playlist please private cloud profile public cloud	panorama patch PC Card vs PCMCIA pixel Preface prohibition sign pull quote push-to-talk
	quarter inch quick key
relationship chatbot reverse video rotate	radix, radixes release notes Restore button rip
server service-level agreement (SLA) service-oriented architecture (SOA) ship single-sign-on (SSO) smb social chatbot software as a service (SaaS) software-plus-services sorry spyware stretch swipe sync	scale up Screen Tip search engine site map software update speed key spider stand-alone Super VGA, SVGA
tab, double-tab, tab and hold target drive, target file terminate text, text message tile, Live Tile toast to-do touchpad touchscreen touch-sensitive trackball trojan turnkey	time bomb title bar titled vs. entitled toolbar toolbox tooltip topic tutorial two-dimensional, 2-D
Ultrabook unwanted software UPnP URL USB use terms	Universal Naming Conversion (UNC) unprintable Usenet utilize
video call, videoconference video card video driver video game virtual agent voice user interface voice-activated device	VCR (videocassette recorder) VGA virtual root virtual server virtualize
wearable, wearable device web services wellbeing white hat hacker whitelist wireless work style workstream worldwide vs. international	Weblication Windows Installer Winsock worksheet
	x86 Xbase zero character

Improve logging with DevTools console object

Every developer uses console.log() to debug in Chrome browser. However it may be difficult to locate your log messages in console window with lots of other messages.  For example:

console.log("event.key=" + event.key)

console.log("isHomeKey=" + isHomeKey)
console.log("isEndKey=" + isEndKey)

Separator

Traditionally we can add a separator to make us easier to locate what we want.

console.log("===========================")

console.log("event.key=" + event.key)
console.log("isHomeKey=" + isHomeKey)
console.log("isEndKey=" + isEndKey)
console.log("===========================")

CSS

Or we can use custom css to make the log messages stand out.  Notice the "%c" in the message as the placeholder of the css.

console.log("%cevent.key=" + event.key, "font-size:1.5em; color:red")

console.log("%cisHomekey=" + isHomekey, "font-size:1.5em; color:red")
console.log("%cisEndKey=" + isEndKey, "font-size:1.5em; color:red")

console.warn()

On the other hand, w can use console.warn() to log the messages. It is displayed with different background color.  We can also use the filter buttons to show warning message only.  Notice that we should use console.warn() in ad-hoc debug only and should not be used in production code for debug purpose.

console.warn("event.key=" + event.key)

console.warn("isHomeKey=" + isHomeKey)
console.warn("isEndKey=" + isEndKey)

Object

We can further simplify by code by creating a ad-hoc object.  Chrome can nicely display key-value pair.

console.warn({key:event.key, isHomeKey, isEndKey})

console.table()

console.table() is another powerful tool for logging. It displays the values in a nicely formatted table.

console.table({key:event.key, isHomeKey, isEndKey})

You can check out the Console API reference for other powerful functions of the console object.

When hardware is cheaper than programmer

I read an article "Learn From Google’s Data Engineers: Don’t Optimize Your SQL". The article is now offline and you can find it in archive.org.

The article is talking about Google engineers made snapshots of tables every few minutes to track dimension history instead of spending time to write complex SQL MERGE statements which seems to be more proper. The author concluded that it makes more sense for Google as the cost of storage of those snapshots is much lower than the salary of the engineers.

mighty Data Engineer’s time is more valuable to move fast and create business value, than to waste their valuable time writing MERGE statements

In recent years, especially in the cloud era, more people prefer to buy bigger machines or add more instances over spending time to optimise the code. I saw new programmers always use simple data structure like array or map in all scenario. They don't have the concept of Big O notation and use brute force to solve problems. I still remember the good old days that we need to optimize the C program with byte alignment, memory pool, and use ++i instead of i++ for the little gain of performance.

Time flies. Now it may not bt economically efficient to spend time to optimize your code. But we need to consider sustainable engineering and carbon friendly coding. Think about that your bad code may exist for 10 years and keep consuming extra energy which can be reduce if you spend some time to optimise it in first place. In addition, optimizing code is fun.  Identifying and fixing performance issues in a program is a challenging and rewarding process. It requires us to use analytical and critical thinking skills to find creative solutions, which can be a satisfying experience. I will spend hours in LeetCode in order to beat 100% of submissions, which I don't need to, but is fun.

Create calendar with Excel formula and conditional formatting

We can build a calendar in Excel with only formula and conditional formatting.

  

Year

Firstly type the year "2022" in cell A1.  You can type any year and the calendar will automatic update.

Day of Week Header

We use COLUMN() to get the day of week numeric value.

In cell B1, type the below formula and copy to B1:H1.

=TEXT(COLUMN()-1, "ddd")

Calendar Body

Then we fill the cells with the days count.

In cell B2, type the below formula and copy to B2:H55.

=(ROW()-2)*7+COLUMN()-1

and offset with day of week of 1-Jan, so "0" is 1-Jan, "1" is 2-Jan and so on.

=(ROW()-2)*7+COLUMN()-1-WEEKDAY(DATE($A$1,1,1))

Now we can construct the date by adding 1-Jan to the days count. Format the date to verify the formula is correct.

=TEXT(DATE($A$1,1,1)+(ROW()-2)*7+COLUMN()-1-WEEKDAY(DATE($A$1,1,1)),"yy-mm-dd")

Finally, we should only show date of current year, and the day of month instead of full date.

In cell B2, type the below formula and copy to B2:H55.

=IF(YEAR((DATE($A$1,1,1)+(ROW()-2)*7+COLUMN()-1-WEEKDAY(DATE($A$1,1,1))))=$A$1,DAY((DATE($A$1,1,1)+(ROW()-2)*7+COLUMN()-1-WEEKDAY(DATE($A$1,1,1)))),"")

Month Header

Next need to show the month in column A. If the row contains first day ("1") means it is a new month.  We can use MIN()=1 to determine if the row contains 1 instead of VLOOKUP(). And use TEXT(..., "mmm") to get the month name.

In cell A2, type the below formula and copy to A2:A55.

=IF(MIN(B2:H2)=1,TEXT(DATE($A$1,1,1)+(ROW(H2)-2)*7+COLUMN(H2)-1-WEEKDAY(DATE($A$1,1,1)),"mmm"),"")

Put everything together

We can put all three formula together. Apply the day of week formula in row 1, the month name formula in column 1. Also show a message in cell $A$2 if the year is not a number.

In cell A1, type the following formula and copy to A1:H55.

=IF(ADDRESS(ROW(),COLUMN())="$A$1","",IF(NOT(ISNUMBER($A$1)),IF(ADDRESS(ROW(),COLUMN())="$B$1","<- Year",""),IF(ROW()=1,TEXT(COLUMN()-1, "ddd"),IF(COLUMN()=1,IF(MIN($B1:$H1)=1,TEXT(DATE($A$1,1,1)+(ROW($H1)-2)*7+COLUMN($H1)-1-WEEKDAY(DATE($A$1,1,1)),"mmm"),""),IF(YEAR((DATE($A$1,1,1)+(ROW()-2)*7+COLUMN()-1-WEEKDAY(DATE($A$1,1,1))))=$A$1,DAY((DATE($A$1,1,1)+(ROW()-2)*7+COLUMN()-1-WEEKDAY(DATE($A$1,1,1)))),"")))))

Format

The calendar if half done now.  We need to add some format.

Select column A:H, apply the below formula in Conditional Formatting.

1. Add the bottom border as month separator if it is the first row (header) or the below is smaller that itself (i.e. next month)

=IF(ROW(A1)=1,TRUE,AND(COLUMN(A1)>1,A1<>"",A2<>"",A1>A2))

2. Add the right border as month separator in same row

=AND(COLUMN(A1)>1,ROW(A1)>1,A1<>"",B1=1)

3. Highlight Sat and Sunday to be red

=OR(COLUMN(A1)=2,COLUMN(A1)=8)

Finally , center align column A:H.  The calendar is done.

In addition, we can protect the worksheet and only allow to input the year.  Right click the cell A1, select "Format Cell", uncheck the "Locked" check box.

In menu "Review" -> "Protect Sheet".