• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a help on filling the timesheet

M

mgesh2002

Member
Dear Ninjas,

Please find attached the excel workbook. I want to fill the 'Result' column (Col. V) based on the total hours of each event.

For Example:
The total number of hours for Event 1 is 9.30 hours, we can round of to 10 hours.
Now, I want to enter number 10.00 on 'Result' Column where 10:00 PM in 'Time' Column,
9.00 on 11:00 PM,
8.00 on 12:00 AM
and so on till
1.00 on 7:00 AM.

So, when user changes the time, the numbers should be populated automatically. If any of you can help me on creating a VBA macro that would be really helpful.

Thanks,
Murugesh
 

Attachments

  • Sales Targets Mar 2015 (1).xlsx
    22.9 KB · Views: 2
You just need some formulas that will update automatically. See if the attached is what you're looking for. I had to add two cells with dates to make the formula easier.
 

Attachments

  • Sales Targets Mar 2015.xlsx
    17.6 KB · Views: 0
Thank you for your quick response.

The formula perfectly works fine on existing time (10:00 PM to 7:30 AM), but when I change the time as 11:00 PM to 8:00 AM, it enters 9.00 on '10:00 PM' of Time Column.

In this case, I want the result like this:
9.00 on 'Result' Column where 11:00 PM in 'Time' Column,
8.00 on 12:00 AM,
7.00 on 01:00 AM
and so on till
1.00 on 7:00 AM.

Actually, users may enter any time as they prefer and excel should populate the numbers accordingly.

Again, I really appreciate your quick reply and for the formula. Can you suggest me any other option?
 
dianacris said:
Combine the formula with an IF statement.
Click to expand...

Hi dianacris,

Yes, the IF statement really worked for Event 1.

Now, I want to make the formula to work for all 4 events. Please find attached the updated excel file. I just made few changes on the formula and applied conditional formatting for color variations.

In this file, I have created Result column for each event (4 columns). Eventually, I want it to be combined as one single column. Please advice.

It will be really helpful if we get the result. Thank you in advance.

Thanks,
Murugesh
 

Attachments

  • Sales Targets Mar 2015 (3).xlsx
    27.3 KB · Views: 0
I'm not sure how to get it all combined into a single column. Now that you have the formulas for the four events, you need to test and determine whether the time in col P is equal to the start time or is between the start/end time. You would have to do that for the event 1, and if it's false test for event 2, and if that's false test event 3, and if that's false then test for event 4. The formula might get pretty ugly. I created some helper columns to start testing, and just to get Y/N values. It might be easier to then create a final nested IF just looking at the Y/N values since the previous calculations are all completed.

As you can see in the helper column AC, the value for 11pm is incorrect. I am unsure how best to make the formula in that column work to accurately tell you which times are within your event 1 time frame.

I used a function called IsBetween which came from the comments in a chandoo blog post. I've included the function and the URL for the post. Add this function to a module if you want to continue to use the function like I did in col AC.

I hope this helps you move forward.
 

Attachments

  • Sales Targets Mar 2015.xlsx
    30 KB · Views: 0
Thought about this while at lunch (and while in a meeting). Came back to it and was able to get the formula to work at the bottom of column AC. How do you want that single column to look? I added a possible result in col AH. I tested changing the event times to see if it continued to work as expected, and it seemed to be. At any rate, this is much closer to a final result.
 

Attachments

  • Sales Targets Mar 2015.xlsm
    35.3 KB · Views: 1
dianacris said:
Thought about this while at lunch (and while in a meeting). Came back to it and was able to get the formula to work at the bottom of column AC. How do you want that single column to look? I added a possible result in col AH. I tested changing the event times to see if it continued to work as expected, and it seemed to be. At any rate, this is much closer to a final result.
Click to expand...

Dear dianacris,

That's Splendid! and you are simply awesome! I am extremely sorry for the delay in response to your messages. I was on a short vacation. I am going to show this to my boss tomorrow and will let you know about the feedback.

Thank you again for all your time, effort and energy.

Regards,
Muru
 
You must log in or register to reply here.
Back
Top