Excel Easter Dates and IP Addresses

As you know, Excel can do almost anything! Recently, I changed the server that my site and blog are on, and Excel helped with the IP Addresses. And since it’s that time of year, let’s see how Excel can calculate Easter dates for us.

Easter Dates Calculation

It’s Easter this Sunday, but if you’d like to verify that, or find out when Easter will be next year, use one of the Easter date calculations that Jerry Latham shared here, a few years ago.

There are several worksheet formulas, and notes on their limitations.
There are also four User Defined Functions, that use different methods for calculating the Easter date.

In the screen shot below, the following formula is in cell B4:

=DOLLAR((“4/”&B2)/7+MOD(19*MOD(B2,19)-7,30)*14%,)*7-6

To download a workbook with the code and formulas, to to my Contextures website.

Excel and IP Addresses

As I mentioned earlier, I recently had my Contextures site and this blog moved to a different server. I hope that it is faster, and has minimal down time.

The only casualty that I’ve found so far is the comment section on this blog. Unfortunately, comments that were posted during the loooong transition time (March 14th to March 25th), were lost in the shuffle.

Other than that, things seem to be okay, but please let me know if you notice anything missing, or broken.

IP Address Conversion

One of the key steps for moving to a new servers was to create DNS records for the them.

The form had boxes for the IPv4 address and the IPv6 address. Uh-oh!

Fortunately, I found examples of the different formats.

IPv4: 192.168.99.1
IPv6: 0:0:0:0:0:ffff:c0a8:6301

To get that IPv6 address, I used an online converter.

How Are IPs Converted?

Looking at those two IP addresses, I couldn’t see how one was converted to the other. More Googling took me to this page that explains the conversion.

Each chunk of the IPv4 address is converted from decimal to hex, to get the IPv6 version.
The hex version each converted chunk becomes 2 characters
Those 8 characters are at the end of the IPv6 address.

When I used the online conversion tool, all the IPv6 addresses started with the same string:

0:0:0:0:0:ffff:

According to Wikipedia, that is the prefix used when PIv4 is mapped to an IPv6 address

Convert the IP Addresses in Excel

Fortunately, my registrar said that the IPv6 version wasn’t required, so I just entered my IPv4 addresses.

But even though I didn’t need one, why not build an IPv4 to IPv6 converter in Excel? It can convert Decimal to Hex, and it’s better than an online tool! Am I right?

Here’s a screen shot of the first few columns on the conversion sheet.

NOTE: This project was just for fun, and might not be accurate for what you need. If you need an IPv4 address converted to IPv6 format, check with your registrar or your hosting company.

Conversion Formulas

There are 3 sets of formulas for the conversion, and a final formula to pull the pieces together.

Dots

These formulas locate the 3 dots in the IPv4 address

=FIND(“.”,[@IPv4])
=FIND(“.”,[@IPv4],[@Dot01]+1)
=FIND(“.”,[@IPv4],[@Dot02]+1)

IP Numbers

These formulas to pull out the decimal numbers, between the dots

=–LEFT([@IPv4],[@Dot01]-1)
=–MID([@IPv4],[@Dot01]+1,[@Dot02]-[@Dot01]-1)
=–MID([@IPv4],[@Dot02]+1,[@Dot03]-[@Dot02]-1)
=–REPLACE([@IPv4],1,[@Dot03],””)

Hex

These formulas convert each decimal number to hex

=TEXT(DEC2HEX([@IP01]),”00″)
=TEXT(DEC2HEX([@IP02]),”00″)
=TEXT(DEC2HEX([@IP03]),”00″)
=TEXT(DEC2HEX([@IP04]),”00″)

IPv6 Address

The formula in column B combines all the pieces, and starts with the mapping prefix (MapPre). The result is changed to lower case

=LOWER(MapPre &[Hex01]&[Hex02]&”:”&[Hex03]&[Hex04])

Convert from IPv6 to IPv4

Then, because why not, I made another set of formulas to convert IPv6 addresses to IPv4.

This was easier, because each chunk is equal length – there’s no need to find the location of each chunk.

These formulas use the HEX2DEC function, to convert the 2-digit hex codes to decimal numbers. You can download the sample file, to see the formulas.

Get the IP Addresses Sample File

To see all the formulas for the IP address conversion, go to the Excel Sample Files page on my Contextures site.

In the Functions section, look for FN0054 – Convert IP Addresses IPv4 to IPv6. The zipped file is in xlsx format, and does not contain any macros.

____________________

______________________