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

  • 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