Results 1 to 5 of 5

Thread: Dates

  1. #1
    Dudeman
    Guest

    Dates

    I do a lot of serial number tracking using Excel, and wondered if there is a way to format the cells to give me the date in "week of the year" format. I've played around some, but out of frustration just made the cells text vs. numeric to do my calculations.

    If anyone knows how I can enter "1/1/01" in the cell and have it display "01", or "12/31/01" in the cell and have it display "52", etc., - I would truly appreciate it.

    Thanks.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Dates

    Hi,
    I don't know of any way to do exactly what you've asked but you could add a column with =weeknum(A1,1) in it to display the week number (where A1 is a cell with a date in it). You need to have the Analysis ToolPak Add-in installed for this to work.
    I would add a note of caution though: the Analysis Toolpak has been known to cause problems with some computers while on others it works perfectly. Try it and see!
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates

    I don't know how you can "format" the cell to return the week number but if you can return the value in another column you can use the weeknum function. You have the choice to designate whether Sunday (1) or Monday (2) starts the week with the return type being set to 1 or 2. The serial date should point to the cell containing the date you need converted. Hope this helps.

  4. #4
    Dudeman
    Guest

    Re: Dates

    Thanks to all who replied. The weeknum works just like you described. Unfortunately, it won't work for me because it turns out I'm looking for a modified Julian Date (YYWW) format. I subtract date of return from date of manufacture to determine warranty status, and was looking for a way easier than adding a column for WW and a column for YY.

    Thanks again for your help and if anyone knows of a way to create the date format I'm looking for, I'd be forever grateful.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Dates

    Not without the use of text, and it sounds like you have already done something like this:

    =TEXT(NOW(),"YYYY")&TEXT(ROUND(NOW()/7-DATE(YEAR(NOW()),1,1)/7,0),"00")
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •