Simple excel calculate date with excel form

December 29, 2012 @ No comments yet

Today I wanna share a simple work based on my daily jobs, an excel form to calculate years and months between two dates. Every years in the earlier of April, we work filtering incoming documents by validating many things including ages of every senders. There are no special application or program we had to help the job. We have to examine all documents manually and write the result into a report list. The report will be added into database later.

The boring part is that we have to calculate the ages manually including years and months. This is the second vulnerable thing from our jobs besides rewriting their ID number. So i wrote this simple excel calculate date form to calculate date between the sender birthdate and present day.

On this article we going to add several excel forms that contain days, months and years that will be combined into a birthdate and present date. All selected date will be calculated with YEARFRAC function to compare date between two dates. If you don’t know how to put forms menu in excel toolbar, kindly read my earlier post about getting forms menu into quick access toolbar.

Designing Form

Select the first sheet and name it with DateCount.  On this sheet design cells for birthdate and present date contain Day, Month and Year columns and also result columns (see the following screenshot).

Preparing cells for dates

Assume you already have forms on Quick Access Toolbar. Insert Combo Box form into day, month and year cell.

Add combo box to cells

Set link from the forms into its related cells (cell behind the forms). To do this right click the form and click Format Control, click Control tab and set Cell Link to the cell behind it. Do this to also on the rest forms. Set vertical alignment for every cells to middle alignment, so the cell value will be hidden behind the form.

cells links

Now we need to go to another sheet, name it with DateArray. On the first column fill it with days (1-31), second column with months (1-12) and the third with years started by year 1901 (see the following screenshot).

array cells

Now go back to DateCount sheet, set input range on the form to the appropriate cells on DateArray sheet (i.e. Day form to Day column in DateArray sheet). To do this right click on form and click Format Control, Control tab and set Input Range as explained previously. Do also to the rest of forms.

date array

Calculate Date

You can now set date for both birthdate and present date (compared to date) by choosing any value from day, month and year shown on Combo Box. But remember as i said on earlier post, linked cells only show index value of the selected data.

For more detail, lets gather all the cells value into one so it’s shown as a complete date value (with “/” as separators).

=B4&"/"&C4&"/"&D4

Where B,C,D = linked cell of birthdate forms (adjust date format to match your computer date format).

combine date

On the screenshot you can see that the year only shown 74 which is the index number of year 1974 on DateArray sheet. We can also tweak the year value by adding the year with 1900, so it will shown as full year value. To do this sum D4 with 1900.

=B4&"/"&C4&"/"&D4+1900

sum value with 1900

Now that both birthdate and present date shown complete date values, we can continue with the result columns. As stated before, we going to use YEARFRAC to count year between two date. YEARFRAC will result decimal number, that’s mean we need to round down the number with FLOOR function.

=FLOOR(YEARFRAC(B6;F6);1)

Where B6 = complete combined birthdate and F6 = complete combined present date (compared to date). Oops,  i forgot to gave name for result columns. From now on the first column will be Year and the second column should be Month.

year calculate

YEARFRAC will compare year between birthdate and present date, in this example result = 10.9194 and FLOOR will round down the value to 10 (with significant value =1).

To calculate month, we need to find the different value between rounded YEARFRAC with the un-rounded YEARFRAC value. In simple word, we need to get the rest value of rounded YEARFRAC and then multiply the value with 12 (month). To do this, simply subtract un-rounded value with rounded one and then multiply with 12.

=(YEARFRAC(B6;F6)-(FLOOR(YEARFRAC(B6;F6);1)))*12

The result = 11.0333 which is a decimal value, so we also need to round down the value with FLOOR.

=FLOOR((YEARFRAC(B6;F6)-(FLOOR(YEARFRAC(B6;F6);1)))*12;1)

complete simple excel calculate date with excel form

The simple excel form to calculate date is complete now. Please note, birthdate have to be less than present date in order to get the right calculated result, otherwise it will give a false result. Have a nice try and have fun.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

Leave a Reply

*

Loading Google+ Comments ...
Loading Facebook Comments ...