• Home
  • About

Entries RSS | Comments RSS
  • Beginning PivotTables in Excel 2007 will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.

  • Categories

  • Archives

  • Recent Posts

    • Create Single Click Desktop Icons
    • Open a Second Window in Outlook 2007
    • Excel Twitters 20090103
    • Excel Twitters 2008127
    • Have an Excellent Christmas!
  • Pages

    • About
  • Subscribe in a reader

Working With Dates In Excel

Posted on November 19th, 2008 by Debra Dalgleish

This week I’ve been working on date formulas, from very simple ones, to complex formulas that calculate workdays per month, based on start and end dates that can span several months.

Extract Information from a Date

Many times I need to pull a bit of information from a date, such as the year, month or weekday.

DateCalcs

Here are the sample formulas I’d use. The date — December 29, 2008 — is in cell A2.

To Calculate

The Formula

The Result

Year =YEAR(A2) 2008
Month Number =MONTH(A2) 12
Month Name (short) =TEXT(A2,”mmm”) Dec
Month Name (long) =TEXT(A2,”mmmm”) December
Day of the month =DAY(A2) 29
Weekday Number =WEEKDAY(A2,1) 2
Weekday Name (short) =TEXT(A2,”ddd”) Mon
Weekday Name (long) =TEXT(A2,”dddd”) Monday
Year Month =TEXT(A2,”yyyy mm”) 2008 12

Using the Calculated Dates

If I plan to create a pivot table from data that contains a date field, I usually calculate the year and month in the source data. Then I can add those fields to the pivot table, instead of the individual dates.

Yes, the pivot table could automatically group the individual dates by year and month, but that can limit other functions in the pivot table. For example:

  • if two pivot tables are based on the same data, grouping one pivot table by month would cause the other pivot table to also be grouped by month.
  • if a field is grouped, you can’t add calculated items to the pivot table

GroupCalcItem

Share/Save/Bookmark

Filed under: Excel

« Avoiding Shared Workbooks in Excel Entering Dates and Times in Excel »

9 Responses to “Working With Dates In Excel”


  1. Comment from Mariusz Cendrowski
    Time: November 19, 2008, 6:45 am

    Hello.
    I am a newcomer here.
    I became a fan of this site from the very beginning.
    Few days ago I tried to make pivot items visible and your idea helped me a lot and what more important drove me to this site.
    THANK YOU VERY MUCH INDEED.
    Now I have a little problem with the date of last save in VBA. I want to show a MsgBox when user opens the file. Of course I can use spreadsheet cell to register the date whenever Excel is saved. But Excel stores this date in workbook properties but I cannot find it in VBA.
    Can you help?

    Best regards

    Mariusz


  2. Comment from Jon Peltier
    Time: November 19, 2008, 7:55 am

    Calculated fields in pivot tables is one of those features that seem like such a good idea when listed in a bullet point, but which don’t live up to their billing. So I’ve usually added a calculation column to the source data.

    It used to be a real pain to add these calculations to the data. Whenever more records were added, you had to drag the formula down to include all of the rows. Then you had to update the pivot table source range.

    With Excel 2003’s Lists (and their descendent, Excel 2007’s Tables), the data source adjusts automatically, and any calculations also autofill themselves.


  3. Comment from Just to thank you about
    Time: November 19, 2008, 6:48 pm

    hello;
    i appreciate highly your blog and i had learned interessting things from you.
    THank you very much.
    I use excel every day more than 10 hours!
    i love excel because he is like women : a lot of sercets and very funy!
    thank you avery much again and again…
    i like very much your book “Beginning PivotTables in Excel 2007″ and i recommanded because your style is so clear that your reader will undertsand your ideas very easily.

    is it difficult to write a book? i plan to make a try…
    your faithfully


  4. Comment from Debra Dalgleish
    Time: November 19, 2008, 8:05 pm

    Mariusz, you can use the FileDateTime in VBA. I’ll post an example tomorrow.

    Jon, you’re right, calculations in the source data are usually less of a headache, than trying to do them in the pivot tables. And it still surprises me when I enter a formula at the top of a table in Excel 2007, and it automatically fills down.

    And yes, ‘Just to thank you about’, it is difficult to write a book. The hardest part is sitting in the chair for the hundreds of hours that it takes, and staying focused. Thanks for letting me know that you liked it!


  5. Pingback from Contextures Blog » Entering Dates and Times in Excel
    Time: November 20, 2008, 12:05 am

    [...] Working With Dates In Excel [...]


  6. Comment from Mariusz Cendrowski
    Time: November 20, 2008, 5:12 am

    Debra, thank you very much.
    This is exactly what I needed.

    Best regards
    Mariusz


  7. Comment from Charlie Hall
    Time: November 20, 2008, 12:34 pm

    Groups are only shared when pivot tables share the same pivot cache (I believe) - if you want two pivot tables to share the same data, but not the same groupings, you can use different range names (which have the same range address) or create the pivot tables each with different number of rows, and then once they are both created, change the addresses to include the same number of rows.


  8. Comment from Ron de Bruin
    Time: November 26, 2008, 1:22 pm

    I always try to avoid the Text function.

    YYYY is jjjj on my Dutch PC

    This is working for Year month

    =–(RIGHT(YEAR(A2),4)&TEXT(MONTH(A2),”00″))

    There are a lot of date functions in DateRefiner that will work in every language version.
    http://www.rondebruin.nl/datarefiner.htm


  9. Comment from Ron de Bruin
    Time: November 26, 2008, 1:27 pm

    Shorter

    =–(YEAR(A2)&TEXT(MONTH(A2),”00″))

    I changed the 2 to a 4 from the 2 digit example in my first post

Leave a Reply

  • Subscribe

    Subscribe in a reader
  • Email Updates

    Subscribe by Email
  • Calendar

    November 2008
    S M T W T F S
    « Oct   Dec »
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    30  
  • Contextures

    • Contextures Excel Pages
    • My Video Tutorials
  • Excel

    • Daily Dose of Excel
    • Excel Team Blog
    • Jan Karel Pieterse
    • JP’s Excel/Outlook
    • PTS Blog
    • Spreadsheet Page Blog
    • TVMCalcs Excel Blog
  • General

    • Hodge Blog
    • J-Walk Blog
    • SuiteMinute