Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie
Hi there,
There is an issue with role permissions that is being worked on at the moment.
If you are having trouble with access or permissions on regional forums please post here to get access: https://www.boards.ie/discussion/2058365403/you-do-not-have-permission-for-that#latest

Excel Date Range Query

  • 20-12-2020 11:00am
    #1
    Registered Users, Registered Users 2 Posts: 174 ✭✭


    Hi,

    I have timesheet information to enter for approx. 30 weeks.

    The information is generally repetitive - i.e. the hours etc. for each day is the same - same hours for each Monday, same for each Tuesday, etc.

    The only thing that changes in the information is the date. That's the part I need help with.

    I have attached the excel file on the left is the raw data for the first week and I have just copied and pasted the information again for the 2nd week (starting on the 18th). The coloured cells on the right are just provided to make it easier for you to see the pattern.

    Is there a way the dates for the remainder of the year can be auto-filled down (to within and beyond the grey shaded cells) without having to manually change the day in each week as I've had to do with the 2nd week in the range?

    Hopefully I've explained it clearly.

    Many thinks for any assistance!


Comments

  • Registered Users, Registered Users 2 Posts: 4,514 ✭✭✭bee06


    I’m assuming the pattern is always the same and is exactly a week after?

    So an easy way would be to (making sure the cells are formatted in date format) is to add 7 to the previous weeks date. Do that for the full week and then copy the full week of formulas into the first cell for the next week and just keep pasting. I’m on my phone so can’t attach and example so I hope my explanation makes sense.


  • Registered Users, Registered Users 2 Posts: 59,702 ✭✭✭✭namenotavailablE


    Could you try in cell A16 the formula =A2+7 and copy down as required?


  • Registered Users, Registered Users 2 Posts: 5,516 ✭✭✭Wheety


    In your first cell (A2) put 11/05/2020
    Then do the following
    (A3) =A2
    (A4) =A2
    (A5) =A2
    (A6) =A2+1
    (A7) =A2+1
    (A8) =A2+2

    etc. all the way down for the first week.

    Then for the next week copy all the date cells and paste. Then you just need to change the first date of a week.


  • Registered Users, Registered Users 2 Posts: 174 ✭✭Dubsey


    Hi namenotavailabl,

    That worked - I'm sure the others would have to.

    Ya gotta love Excel.

    Many thanks guys.


Advertisement