Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    week days (2003 Service pak2)

    I have a work book for each month of the year. Each workbook contains a sheet for each day of the month. One sheet collects data for each day of the month from multiple sheets in the workbook. I am now entering the day of week by hand. See attachment column B.
    How can I have excel do this? I am fairly new at this and would appreciate any help.

    Best Regards,Bill
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: week days (2003 Service pak2)

    Hi Bill

    You can use custom lists.

    1) Tools | Options
    2) Click Custom Lists Tab
    3) Click the modal button next to Import from Lists
    4) Highlight cells B12: B18 in your book
    5) Click the little red arrow to the right of the options window
    6) Click Import

    This is how to create a custom lis for any type of special list.

    Now you can type Mon in a cell and then using the fill handle click and drag down the column and it will automatically fill the rest of the days of the week.
    Jerry

  3. #3
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: week days (2003 Service pak2)

    Hi Jerry, thanks for your reply. I'm trying to get Excel to input the correct weekday name based on the date, Sorry I did not make it more clear with previous post.

    Best Regards, Bill

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: week days (2003 Service pak2)

    I used custom format:

    In the A column Format/Cells/Custom d
    In the B column Format/Cells/Custom ddd

    You will nee to enter the first date of the month in A10 as 12/1/07 for example.
    Attached Files Attached Files

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: week days (2003 Service pak2)

    OOPS you need a formula in B10 -- =A10 and copy down

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: week days (2003 Service pak2)

    My bad, sorry, I made an assumption <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    In cell B10 type:

    =DAY(DATE($D$3,$B$3,A10))

    Format the cell as Custom format "DDD" and then copy down using the fill handle

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>This should correct your day errors <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: week days (2003 Service pak2)

    Another option is to put the date in cell B10 and format it to display the day of the week:
    =DATEVALUE($B$3&" "&A10&", "&$D$3)

    You can copy/autofill to B40.

    Format the cells, custom as ddd and it will contain the date, but display the day of week.abbreviation.

    [on a side note, if instead of separate sheets for each Day and separate books for each month, you could combine them into one file, these types of summaries can be done automatically with a Pivot table. I am not a big proponent of having a workbook with multiple sheets that are identically formatted. I find it so much easier to handle with combined database...]

    Steve

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: week days (2003 Service pak2)

    Alternatively, enter this formula in B3:

    =DATEVALUE(A10&"-"&$B$3&"-"&$D$3)

    Apply a custom format of ddd to this cell, then fill down as far as needed.

  9. #9
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: week days (2003 Service pak2)

    Thanks very much Stans and Jezza, appreciate you folks!

    Best regards, Bill

  10. #10
    Star Lounger
    Join Date
    May 2007
    Location
    California, USA
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: week days (2003 Service pak2)

    Thanks to all that responded, some great tips very much appreciated. Steve, I would agree 100% with you about all the identical sheets, its lots of work too. My expertise in Excel being limited I'm not sure if I would be up to the task of using pivot tables. I will look into it. Now that I am aware of such great help as this board my confidence level will be much improved.
    Any tips you would be willing to pass along on how to convert to pivot tables would be appreciated.

    Best regards, Bill

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: week days (2003 Service pak2)

    Pivot tables are relatively straightforward once the data is setup correctly.

    If you have identically formatted sheets and they are all setup as a "table", all it would take is adding a new column (to designate what sheet) and copying each sheet one after the other and then fill in the sheet info.

    If they are not setup as a table, then some designing needs to be done to determine how to sit it up to get what you need out of it. We would need much more details of what you are doing before we could give any specific advice

    Steve

Posting Permissions

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