Tag Archives: Dates

Excel Tips: Calculate the weekday from a date

You can display the weekday for dates stored in Excel.  This is especially useful for checking if due dates fall during the working week.  To do this you need to use the weekday function and custom formatting features of Excel.  The screenshot below are from Excel 2010, but this work in earlier versions too.

  • Ensure the date on your spreadsheet is formatted as a date
  • Insert the weekday function
  • The weekday function has two arguments – the date (or cell where the date is and a return type.  The return type is optional and for this to work correctly should be left blank. 
  • Format the cell using custom formatting – to do this you will need to open the format cells dialog box, Ctrl+1 is a keyboard shortcut to do this which works in all Excel versions.

On the Number tab, choose Custom and in the Type box enter ddd  to display the week day in its three letter version or dddd to display the week day in full.  

Excel 2010 Tips Autofill and Dates

Autofill is a feature which will copy content down a column or accrss a row.  However it also creates sequences, using the same technique, with things that it recognises as being part of a sequence.  One such is dates.

Enter the starting date of your sequence in Excel and ensure it is formatted as a date

Select the cell containing your date and drag down from the autofill handle (the autofill handle is the little black square in the bottom right hand corner of the active cell.

A tooltip will appear showing you the date that you have reached as you drag the autofill handle down or across you spreadsheet.

This method works with any date format 

and even with multiple columns

As well as full dates and days of the week, autofill will complete a sequence of months and complete a sequence of the three letter names for days of the week or months.  Simply enter the day or month you wish to start on and autofill will do the rest.

Excel training:Calculating with Dates

One of the most asked questions during our Excel 2010 Training Courses has to be the subject of calculating with dates.

Excel stores dates (and times) as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day (e.g. 31/10/2011 10:00 is stored as 40847.42).This is called a serial date, or serial date-time.

To calculate the difference between two dates:

Method – Subtract the earlier date from the later one:

  1. Input your dates into two cells on your spreadsheet
  2. Create a formula which subtracts the earlier date from the later date(e.g. =A2-A1)
Sample Date Difference Calculation

Sample Date Difference Calculation

3.  Format the result to be a number with no decimal places, using either the Number group on the ribbon  or Format Cells dialog box

To calculate a date:

Method – Add the lead time to the start date.  This method can be used to calculate anticipated delivery or payment dates.  The worked example below relates to a delivery date:

  1. Input your start date (in this example the order date)
  2. In another cell input the delay (in this example the lead time to delivery).  This should be in days
  3. Create a formula to add the days to the date (e.g. =A1+A2)
  4. Format the result as a date, using either the Number group on the ribbon  or the Format Cells dialog box.
Sample Date Calculation

Sample Date Calculation