Calculating annual hours!
Users enter task and then choose 'Frequency" from drop down. Frequency can be daily, weekly, monthly, bimonthly, qtrly, annual, et. Users enter time committed in HH:mm forma, blue columns. I need to calculate annual hours committed base on the frequency chosen and the hours entered, yellow column.
Hi. Good Day.
Can't pivot table resolve your issue? Pls check the attachment. Pivot providing the answers you are looking for? Please confirm if this is what you need?
Thank You.
Regards.
Perpectuals
Thank you so much for your response!! I need each task entered annualized. I will then go back and group similar tasks to determine time dedicated to a value stream.
For example, Task = Open Mail, Frequency = Daily, Time Dedicated = 00:15, Annual time = ???. All based on annual working hours of 2080. I inherited this sheet so it may be a
much easier way to calc. Thanks again.
Hello Dat1ua95 and Welcome to Excel Forum.
My suggestion would be to produce a conversion table as modeled in Y4:Z9
The formula for L2 and down could then be: =K2*VLOOKUP(J2,Y$4:Z$9,2,0)
I suggest using the following custom format for column L: [h]:mm
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you! This is the answer. I appreciate you explanation and solution!
Alternate solution: =K2*LOOKUP(J2,{"Annually","Bi-Weekly","Daily","Monthly","Quarterly","Semi-Anually","Semi-Monthly","Weekly"},{1,26,365,12,4,2,24,52})
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Re: Post #5, You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' as Alan stated. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks