Using times with currency calculations
Video: Using times with currency calculationsIf you are working with time entries and currency in the same worksheet, the calculations might not be quite what you'll expect. Let's look at the information in Column B. This represents Time Worked 5 hours, 7 hours and 15 minutes, and we have a Rate that someone is presumably being paid for. We need a formula here. You can see on the first example 5 hours, $10:00 an hour. We know what the answer is. It's going to be $50. So it's just a simple multiplication. Take this, times this, and sure enough our answer is not even close.
Viewers: in countries Watching now:
In Excel 2010: Working with Dates and Times, Dennis Taylor shares his solutions for optimizing the use of dates and times in Excel 2010. This course explains what's going on behind the scenes when Excel stores dates and times, gives tips for entering dates and times, and shows options for date and time formatting. It also demonstrates the various date and time functions and shows how to calculate with dates and times in a range of scenarios. Exercise files are included with the course.
- Understanding how Excel records and stores dates and times
- Looking at standard date/time entry options and acceptable alternatives
- Using the TODAY and NOW functions
- Customizing date formats
- Exploring keyboard shortcuts
- Formatting time for hours over 24
- Calculating differences across dates and times
- Rounding calculations
- Working with holidays
- Validating with dates
- Converting formatted dates to usable data
Using times with currency calculations
If you are working with time entries and currency in the same worksheet, the calculations might not be quite what you'll expect. Let's look at the information in Column B. This represents Time Worked 5 hours, 7 hours and 15 minutes, and we have a Rate that someone is presumably being paid for. We need a formula here. You can see on the first example 5 hours, $10:00 an hour. We know what the answer is. It's going to be $50. So it's just a simple multiplication. Take this, times this, and sure enough our answer is not even close.
It does point the problem that we have sometimes in working with time data. Now, remember times are recorded as portions of days. One approach to these when you can't figure out what to do is to think, well, if it should have been 50, and it's 2.08. What's the multiplier? Well, probably not so obvious at first, but we do know about 24 hours a day, and let's try that. Of course, not just try it. It does make sense here. We need to take this calculation and multiply it by 24, and that's the correct answer of course.
We'll copy these down the column. Sure enough, here and there you can see some of them immediately. 8 hours, $20 an hour, it's $160. So, when performing these kind of calculations at first may be not so obvious, but you can quickly figure it out what the total should be. In Column F, we've got some Down Times, and these are minutes. 5 minutes, 7 minutes, 7 minutes, and there is a charge for the Down Time and here is the Rate. You can take a look at one or two here and there. For example, this one here it's down 10 minutes and it costs $7.50 a minute so $75. But here too I think the first starting point might be you are going to multiply the two in a particular order.
Come up with an answer here. Of course, you often would have a rough idea. It's what? 25, 30 bucks? Well, certainly not 2 cents. So, what do we need to do here? How many minutes in an hour? How many hours in a day? 24 and 60. Not so obvious. You can do the math and come up with 1440 or do it this way. This is probably slightly a better documentation. There we go! That looks like a sensible answer, and let's take a look at some of the others. Double-click. One or two of these, you might be able to do in your head pretty quickly, but say this one here, the one we've mentioned.
Yeah, 10 minutes, it's $7.50 a minute, $75. So, in calculations like this, in many cases use the number 24, sometimes the number 60. Time calculations can be tricky, but there certainly are some solutions here that let us work with this data pretty smoothly.
There are currently no FAQs about Excel 2010: Working with Dates and Times.