Today's Date

  • CTRL ;

How do you Highlight Dates Before Today

  • We will use conditional formatting
  • Select column of dates
  • Go to the Home tab
  • Select New Rules from the Conditional Formatting drop down list
  • Select "Use a formula to determine which cells to format"
  • Enter the following formula

  • Click the format button on the dialogue box
  • Select Fill and yellow as the fill colour
  • Click O.K. on the fomat and O.K. on the Conditional

How to change the format of a date displayed as a serial number

The results of a formula involving dates is quite often displayed as a serial number
The the formula changing a serial number date to other a date format is as follows
To specify the date format within the formula that you are using
=IF(G1>H1,TEXT(G1,"dd-mmm-yyyy"),TEXT(H1,"dd-mmm-yyyy") )

Determining the Difference Between Dates

View instructions for determining the difference between dates in Excel

Keying in the formula =today() and press enter. the result is today's date will be displayed
to determine the difference between dates the formula used is =datedif(start date, end date, units)
units= "d" for days, "m" for months, "y" for years
in our example we will used the begining of the year, today, and days as units
=datedif(01/01/2013, today(),"d")

Using the Workday Function

Video on Workday function

  • The workday function allows you to calculate the date from the start date and a set number of working days.
  • Enter a date and in another cell enter the number 25 as the number of work days
  • Click on the insert funtion button
  • From the dialogue box, select workday and click OK
  • Select the date that you have previously entered on your sheet
  • Select the number 25 as the days
  • Enter the number 2, in the Holidays field
  • Click OK

  • The serial number of the date is returned
  • Select the first date that you entered on your sheet
  • While it is selected, click on the Paint Brush icon on your Home tab
  • Click on the serial number that was reutrned by the funtion
  • The date will be displayed in the same format as your original date

End of Month Function

View Video on End of Month Function

My videos are all created on Camtasia.
TechSmith Camtasia Click Here

  • The end of month function opens a wide variety of date calculations
  • Eomonth(start_date, Months)
  • Dates must be entered in numeric
  • Months can be enter in positive or negative numbers

  • =EOMONTH(A1,2) gives you the last day the the next quarter

  • The eomonth function always returns the date serial number
  • To change date from a serial
  • Using a negative month value, you are able to determine the last day of the previous quarter

  • You are able to calculate the number of workdays in the period by Networkdays

Related Videos

The Days Function
Networkday Function
Workday Function
Day of the Week