Excel 2010 Basic Arithmetic Operation On DATE Values

Excel 2010 provides an easy way to fill out fields with Date format values. Unlike other spreadsheet programs, it includes a list of functions that can be used to manipulate Date values in the data sheet. For example, it lets you apply add and subtract date values, and shows you the result in the defined date format. If you’re dealing with multiple fields containing Date values and want to apply some basic arithmetic functions, then it may become a bit difficult to manage all the date values . In this post we will be focusing on how to insert Date format values and perform some basic arithmetic operations over them.

To get started, launch Excel 2010 spreadsheet on which you want to apply simple arithmetic Date operations. For Instance we have included a spreadsheet containing fields; S.No, Date of Commencement, Due Date and Difference.

date

Start filling out Date fields instantly by entering values as fractions, it will automatically convert them into Date value. For example if you need to enter 4-June, then enter the month number followed by a slash (/) and Day value (4).

table value

To convert it into long or short Date form, navigate to the Home tab and from Number group, select the desired Date form.

date value

Now start filling out both field values in desired Date form.

table

For finding out the difference, we will be using a basic subtraction operator.


=C2-B2

In function arguments, B2 is the location of cell in Date of Commencement field and C2 refers to the Due Date field value. As you can see in the screenshot below that it will yield the desired result in the simple integer format.

230 days

Drag the plus (cross) sign at the end of the cell towards the end of the column to apply it over.

apply

To find out the Due Date, add the number of days in the Date of Commencement field. We will be using the sum operator:

=B2+C2

In the above formula, B2 is the location of the cell where the date of commencement value is residing and C2 is the location of Remaining field value. It will show a due date in Date format instantly.

table 2

It’s highly advisable to check the date values on which you are applying basic arithmetic functions, as for some calculations it may show you incorrect results without throwing any errors.

You can also check out our previously reviewed Excel functions; LARGE, CEILING, RANK, TIME, ADDRESS ,FACT (factorial), MAX,MIN, MAXA, MINA and EXACT, Logical Functions, INFO, SUMSQ, DOLLAR, SUMPRODUCT, SUMIF, COUNTIF, VLOOKUP, HLOOKUP ,PMT, & LEN.

Advertisement