Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    2

    Unanswered: Distribute Time Spent in 30 min Intervals

    I was looking for a formula / VBA script in Access where if I have a start time and end time, it would distribute the time between start and end in 30 min intervals. Example of my raw data is as below

    Column A to E is the raw data and the rest on the right is the time interval. If oyu look at Column Y to AA, you will see the distribution as it needs to appear.

    Format of the table is

    Employee Name exception start stop 09:00 09:30 10:00 10:30
    Employee 1 Lunch 09:10 10:10 20 30 10 0

    where employee name, exception, start and stop are imported form a different source and the ones on the right get updated depending on the time spent in that 30 min interval. This will have 48 columns in total (I have listed just 4 here due to space constrain) but depending on the time mentioned, it should distribute the time in the respective 30 minute intervals.

    Thanks,
    Maddy
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats a seriously poor design
    you already have the data (the clock on and clock off), why do you believe you need to store that in 30 minute intervals. thats derived data, and can always be pulled back whenever required
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2012
    Posts
    2
    HI,

    What we need is the amount of work done in various mode broken down in 30 minute segments. All i am looking for is a VBA script or a potential nested query which can put break the time into segments and distribute this in 30 minute segments (As shown in the excel attachment)

    Thanks,
    Maddy

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you are proposing violates normalised data design
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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