Excel Date Functions That Are Worth Using

Applications of Excel Date Functions.

All Business Transactions Are Recorded On the Date It Occurs.

When working with big data sets which contains date data, it is important to use the correct Excel Date Functions. The Excel Date functions will help analyze data much faster and  to get much needed information. In every important business data sources like daily sales, invoices, payments, bank transactions and etc are all recording according to the date. As such analyzing date using the date data is very crucial for any business operations. Most senior management people  will require data analyzed and presented to them in periodic orders. For example, monthly, quarterly or annually to make comparisons, do predictions or to depict business activity trends.

Scheduling of Project Activities and Allocation of Resources In Project.

Excel date functions are also needed for scheduling of activities. In any type of project, activity or resource schedule is key. This will  to determine costing like man hours and to mobile much needed resources to get the project completed within schedule, scope and budget. Schedule, scope and budget are what most project professionals refer to as the “three legged stool”. All depends on each other and changes in one affects the other two.

In Human Resource Management.

A lot of human resource management activities depends on date data. For example, calculating staff leave days, Over-time, travel schedules, staff retirement planning and calculation of entitlements.

Excel Date Functions ().

There many Excel Date() functions but here are the ones that most people would be utilize to be productive in their respective jobs.

  1. Excel Year(), Month() and Day() Functions

In most cases date data excluded  in given in one column with the format dd/mm/yy  (eg 12/03/15 ) and other date formats. As part of the data analysis process, the year, month, and day can be extracted from this date column.  Syntax are as follows:

=Year(Serial_Number)

=Month(Serial_Number)

=Day(Serial_Number)

The (Serial_Numer) is the cell reference of the date data.

For example, in the following Excel data set, the formula are as follows:

B2= Year(A2)

C2 =Month(A2)

D2=Day(A2)

Excel Text() Function

Note that Month and Day functions returns the numeric value of the month. Not the name of month (eg August) or day (Wednesday).  If you require the name of month or day, then use the Text() function.

C2 =Text(A2,”mmm”)

D2= Text(A2,”ddd”)

Note: To get the full name of month and date indicate as “mmmm” or “dddd”.

2: Excel  Days() and NetworkDays() Function.

Days()  calculates the total number of days between the End Date and Start Date. NetworkDays() function calculates the working days between the StartDate and the EndDate

Syntax are as follows

=Days(End_Date,Start_Date)   eg  C2 =DAYS(B2,A2)

=NetworkDays(Start_Date,EndDate)  eg   D2=NETWORKDAYS(B2,A2)

Results as follows

This will help know the total days of work if work has to be non-stop without weekend breaks and total number of weekends, if there has to be weekend break or to calculate overtime hours.

3: Excel DateValue() Function

In order to calculate date  data, the data in the date column need to exist in Date Format. If the data comes in column in text date format (example, 12-02-21  or 12/02/21 then it needs to be converted to date data. DateValue Function helps does that.

4: Calculate Difference Between Two Dates in Excel.

The calculate the difference between days simply add or subtract the required number of days from the given date.

Eg

=A2-10

=A2+10

Just make sure the results column’s Number format is Numbers.

5: Excel Today() Function

Today() Function  returns the date for today.  Today() is useful if you are monitoring  a project completion date  or some important dates like BirthDays, Weddings or other important appointments.

=A2-Today()

A2 cell contains the date of the event/appointment. The result cell will count down the number of days remaining each day.