{"id":334,"date":"2011-10-06T10:40:25","date_gmt":"2011-10-06T10:40:25","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=334"},"modified":"2023-12-30T23:13:37","modified_gmt":"2023-12-30T23:13:37","slug":"excel-trainingcalculating-with-dates","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/excel-trainingcalculating-with-dates\/","title":{"rendered":"Excel training:Calculating with Dates"},"content":{"rendered":"

One of the most asked questions during our\u00a0Excel 2010 Training Courses<\/strong> has\u00a0to be the subject of calculating with dates.<\/p>\n

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\u00a0serial date<\/strong>, or\u00a0serial date-time.<\/strong><\/p>\n

To calculate the difference between two dates:<\/strong><\/strong><\/p>\n

Method – Subtract the earlier date from the later one:<\/p>\n

    \n
  1. Input your dates into two cells on your spreadsheet<\/li>\n
  2. Create a formula which subtracts the earlier date from the later date(e.g. =A2-A1)<\/li>\n<\/ol>\n
    \"Sample<\/a>
    Sample Date Difference Calculation<\/figcaption><\/figure>\n

    3. \u00a0Format the result to be a number with no decimal places, using either the Number group on the ribbon\u00a0\"\"<\/a>\u00a0or Format Cells\u00a0dialog\u00a0box<\/p>\n

    To calculate a date:<\/strong><\/strong><\/p>\n

    Method \u2013 Add the lead time to the start date.\u00a0 This method can be used to calculate anticipated delivery or payment dates.\u00a0 The worked example below relates to a delivery date:<\/p>\n

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

       <\/p>\n

       <\/p>\n","protected":false},"excerpt":{"rendered":"

      One of the most asked questions during our\u00a0Excel 2010 Training Courses has\u00a0to 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\u00a0serial date, or\u00a0serial […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[78,148,171,482,539],"class_list":["post-334","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-calculate","tag-dates","tag-excel","tag-sf","tag-training"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/334","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=334"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/334\/revisions"}],"predecessor-version":[{"id":6777,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/334\/revisions\/6777"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=334"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=334"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=334"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}