Last year the same day we learnt how to make a birth date heat map in Excel. So lets another Excel trick exactly one year after.
Lets say the date of birth is August 6, 1986. And here are few things you want to find out only by punching your birth date:
- How many days until next birthday?
- What day will be your next birthday?
- What is exactly your age in today’s date?
- How old will you be by your next birthday?
And few other things to remember…
So lets get started…
Separation – Counting days… moths… years…
Step 1: Open Excel workbook and in cell B2 write Date of Birth whereas cell C2 holds your date of birth. In our case it is August 8, 1986 so it will be written like this: 8/6/1986
Step 2: In cell B4 write Day of birth, cell B5 write Month of birth, cell B6 write Year of birth.
Step 3: In cell:
C4 put this formula: =DAY(C2)
C5 put this formula =MONTH(C2)
C6 put this formula =YEAR(C2)
Step 4: In cell B8 write Today’s date: and in cell C8 put this formula: =TODAY(). This formula with empty brackets will print the current date which in our case is 7/7/2014.
Connection – The day you born… and present…
To calculate the date of birth for this year… we will need part of information from date of birth i.e. 6-8-1986 and part of information from 7-7-2014 to make the information complete…
Step 1: In cell B10 put this formula:
=”In “&YEAR(C8)&” your birthday”&IF(C8>C10,” was”,” is”)
Step 2: In cell C10 write this formula:
DATE function will combine the numerical values in cell C4 and C5 that we separated earlier from birth date. To extract the year from today’s date we applied the same YEAR function that we used on birth date.
Step 3: In cell B11 put this formula:
=IF(C10<C8,”Your Next Birthday is”,””)
Step 4: In cell C11 put this formula:
Questions – Things to remember…
Now coming to those questions that we asked in the beginning. As we have already separated and connected the information as per our wish… we can find the answers… that we can remember easily forever…
Step 1: In cell B13 write Birthday status and in cell C13 put this formula:
=IF(C8>C10,”Too late! Next birthday in “&C11-C8&” days”,IF(C10=C8,”Happy Birthday To YOU!”,”Your birthday is in “&C10-TODAY()&” days!”))
Step 2: In cell B14 write Day on next birthday and cell C14 type this formula and press Enter key:
Step 3: With cell C14 still selected hit Ctrl + 1 shortcut to invoke the number format options. From the dialogue box that appear, select custom from the list at the left and under the type input field key in dddd and click OK button. This will show just the day of the date.
Step 4: In cell B15 write Your current age and in cell C15 type this formula:
=DATEDIF(DATE(C6,C5,C4),TODAY(),”y”)&” years “&DATEDIF(DATE(C6,C5,C4),TODAY(),”ym”)&” months “&DATEDIF(DATE(C6,C5,C4),TODAY(),”yd”)& ” days”
Step 5: In cell B16 type By next birthday you will be: and in cell C16 type this formula:
Following snippet shows the working… breathing calculator…
My prayers and wishes to all those born the day August 6, 1986 around the world. May all of you live a super cool life full of happiness and joy. May you never see anything less than awesome what nature has to offer in the best of its colors. You people have made the lives of so many so beautiful already… May you never age… and always stay strong and forever young… May ALLAH shower the best of HIS blessings upon you…