Hello Formula Friday Excellers, today let’s look at a really handy formula to help us calculate how many hours a total task from start to finish will take. The whole task is made of a lot of mini tasks that only take a few minutes each.
I have calculated (in my day job) the length of time that a total task (in this case updating database records) would take when we have multiple records to update. The estimation is that it will take 3 minutes to individually update each account record in the database, and we need to know how many hours it will take in total to update.
I have analysed my record set and have 3,500 records to amend out of my 5,000 records in my database. So the first bit is easy- calculating the total amount of minutes to update 3,500 records at 3 minutes per record.
So the total tak will take 10500 minutes, and what is that converted to hours?. Easy…use the CONVERT Function!.
The syntax of the formula is
=CONVERT(number,from_unit,to_unit)
Where
- CONVERT this is the number we want to convert
- from_unit the unit type you are converting from (in this case minutes)
- to_unit the unit type to convert our number to (in this example it is hours)
Let’s build the formula and get this calculated with the first stage of the formula by hitting =CONVERT(D7
Excel will provide you with a drop down of all of the conversions it can conveniently carry out for us- nice. We can then go ahead and complete the formula….
Close off the formula with a closing parentheses and the job is done.
Phew 175 hours to do all of that work updating all of those records…….
The Convert Function had a tonne of useful unit conversions, you can see just a snippet of them in the screen shots above. For a full list-see the microsoft website or just type in the function and scroll up and down to see how many are available. Really useful.
- View all of your Excel formulas at once
- Hide all of your Excel formulas from prying eyes!
- Undertand and debug Excel formula errors
- How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
Read My Review Of Chandoo’s VLOOKUP Book Here
[amazonjs asin=”1118460847″ locale=”US” tmpl=”Small” title=”Excel Formulas and Functions For Dummies”]
[amazonjs asin=”1118902688″ locale=”US” tmpl=”Small” title=”101 Ready-to-Use Excel Formulas (Mr. Spreadsheet’s Bookshelf)”]
[amazonjs asin=”B009FD7J2K” locale=”US” tmpl=”Small” title=”Mastering Excel Formulas IF, AND, OR”]