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
My videos are all created on Camtasia.
TechSmith Camtasia Click Here
|
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
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
- =NETWORKDAYS(A1, E1)
Related Videos
| The Days Function |
| Networkday Function |
| Workday Function |
| Day of the Week |
