Hello all - this is my first post. I'm sorry if I don't know what I'm saying - it's because I'm not 100% sure of what I need to use in a formula to achieve my goal. Let me explain. I am a translator and editor. As part of starting with a clean slate in 2017 I've decided to try to further hone my ability to predict how much time I will require for different types of job.

I have broadly two types of regular work coming in: copyediting and translation. Editing usually takes me about half the time but it depends on the complexity of the task. Within each category, there are different levels of difficulty and therefore, time required to do a job. I realized that I could categorize these (as I already do for billing purposes) as essentially, and for my purposes, "Easy/Medium/Hard".

Having looked at some past numbers (I still need to do an in-depth analysis) and doing some back-of-envelope calculations I realized I could base my daily productivity on the number of words I can process for each category whilst also factoring in all the other stuff I do for my business (not directly related to actually processing words + brain-rest time).

I came up with the following codes for Copyediting and Translations:

CE1 = 962 words/hour or 4810 words/day
CE2 = 811 w/h or 4055 w/d
CE3 = 685 w/h or 3425 w/d

Tx1 = 642 w/h or 3210 w/d
Tx2 = 550 w/h or 2750 w/d
Tx3 = 208 w/h or 1040 w/d

I want to be able to plug these numbers into excel, combining them with the word count for each job that comes in to give me an estimate — preferably in days/hours/minutes — of the estimated time I will need to do each task. Currently, I am very bad at appreciating this based on the excel tables in front of me so an actual number would be a great start. It would help me with customer quotes and deadline planning, and to know when I might need to out-source work, which I am also really bad at noticing. I like this idea because it means I can tweak the figures as necessary over time, and perhaps even add in extras as necessary.

A very simplified version of my current work management template looks a bit like this, where the "service" refers to the speed at which the customer requires their work be returned:

A1 A2 A3 A4 A5
1 Project Ref: Service: Code: Words: Estimated Time Required:
2 Cactus Book 2 months Tx2 32482 dd:hh:mm
3 Active commuting to preschool 7 days CE2 3311 dd:hh:mm
4 Real Estate Descriptions January 24 hours Tx1 781 dd:hh:mm
5 The Dark side of cultural policy 4 days Tx3 2402 dd:hh:mm

I'm looking for the way to fill in the dd:hh:mm section automatically via a formula(s).

As an added bonus, if anyone has any idea how I might be able to graphically display this — is it a Gantt chart? or something similar — I will personally purchase the internet for you and deliver it to your door in person, with flowers, and a card! hahaha! :')

I apologize for this long and winding post, but I don't know what I need or what to call it, or how to solve this problem which is becoming ever more important as my business grows. I hope you are willing and able to help or point me in the right direction! I have tried to be detailed to avoid misunderstandings and confusions!

Best wishes to all.
Maria