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 =TEXT(G1,"dd/mm/-yyyy") |
 |
| 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
- 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
- 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
- =NETWORKDAYS(A1, E1)
Related Videos
| The Days Function |
| Networkday Function |
| Workday Function |
| Day of the Week |
