| View previous topic :: View next topic |
| Author |
Message |
Mackerg5
Blue Pill
Joined: Dec 19, 2007
Posts: 2
|
 Posted: Wed Dec 19, 2007 5:03 am Post subject: Simple Time Calculation (Spreadsheet) |
|
Hi,
I'm the first to admit that I'm not an accomplished spreadsheet person so I'm sure there must be a better way to do what I'm trying to do.
I'm trying to do a time sheet calculation. I want to insert an elapsed time value and from that work out a monetary value using a rate per hour.
So I want to insert a value in A1 of, say, 3.30 for three and a half hours. In the next cell - A2 is the fixed hourly rate, say 20 pounds. In the next cell is the calculated value, so the calculation would be =A1xA2. But of course the minutes mess this up.
The way I'm doing it at the moment is by having two general numeric cells for the time: A1 for hours, A2 for minutes and then A3 for the hourly rate. I'm then using the calculation for the result as =(A1*A3)+(A2/60*A3)
It works but I'm sure there must be a more elegant way of doing this i.e. one which allows me to put the time value in just one cell. But I can't work out how to do it. I also want to distribute the spreadsheet to others (probably as an XLS) so I want to keep it simple for them to follow.
Any help appreciated. |
|
| Back to top |
|
 |
ovvldc
Captain Naiobi
Joined: Sep 13, 2004
Posts: 2352
Location: Zürich, CH
|
| Posted: Wed Dec 19, 2007 5:38 am Post subject: |
|
Just disassemble the process that you do when you calculate the amount in your head.
Try splitting off the hours from the minutes. Round down your time to get the hours. Subtract the hours from the total time and multiply by 100 to get the minutes. Divide minutes by 60 to get fractions of hours. Add the fractions and hours and multiply that by hourly rates.
good luck,
Oz
P.S. We get increasingly numbers of these exceedingly general office suite questions. I feel this is good (NeoOffice is not scaring off newbies) and bad (this is not support specifically for NeoOffice). Does anyone know of any general resources/handbooks/tutorials that we can point these people to?
_________________
"What do you think of Western Civilization?"
"I think it would be a good idea!"
- Mohandas Karamchand Gandhi |
|
| Back to top |
|
|
Mackerg5
Blue Pill
Joined: Dec 19, 2007
Posts: 2
|
| Posted: Wed Dec 19, 2007 6:38 am Post subject: Thanks |
|
Thank you for pointing me in the right direction i.e. ROUNDDOWN
Here's the calculation I used:
=((((ROUNDDOWN(D2;0))*60)+((D2-ROUNDDOWN(D2;0))*100))/60)*E2
D2 being the elapsed time and E2 the hourly rate.
I'm sorry if this was inappropriate for this forum. Won't do it again.
Bob. |
|
| Back to top |
|
|
Samwise
Captain Naiobi
Joined: Apr 25, 2006
Posts: 2315
Location: Montpellier, France
|
| Posted: Wed Dec 19, 2007 6:46 am Post subject: Re: Thanks |
|
| Mackerg5 wrote: | I'm sorry if this was inappropriate for this forum. Won't do it again.
Bob. |
No, it wasn't inappropriate at all. Oscar was just asking our other regulars whether there were any good resources we could point beginners to, as we tend to answer the same questions over and over again…
Edit: BTW, if you have other questions about NeoOffice, some of them are already answered:
1) in the built-in Help files (although they're not always very accurate);
2) the NeoOffice User Guide (link);
3) the Wiki (http://neowiki.neooffice.org/).
If you don't find your answer there, feel free to ask on the forums  |
|
| Back to top |
|
|
ovvldc
Captain Naiobi
Joined: Sep 13, 2004
Posts: 2352
Location: Zürich, CH
|
| Posted: Wed Dec 19, 2007 2:58 pm Post subject: Re: Thanks |
|
| Mackerg5 wrote: | | I'm sorry if this was inappropriate for this forum. Won't do it again. |
It is not inappropriate, don't worry. However, our resources (man-hours) are limited and we may not answer every question (at least I don't).
-Oscar
_________________
"What do you think of Western Civilization?"
"I think it would be a good idea!"
- Mohandas Karamchand Gandhi |
|
| Back to top |
|
|
gilescooperuk
Agent
Joined: Dec 06, 2007
Posts: 14
|
| Posted: Thu Dec 20, 2007 3:57 pm Post subject: |
|
At work I have a prototype spreadsheet in excel that does a similar thing.
You input start and end time and it works out the number of hours worked and then the correct overtime rate for the company.
It is an excel sheet but I can retrieve it when I am back in work on the 27th (having 2 weeks holiday and left laptop, mobile, secureid all in the office!)
Giles |
|
| Back to top |
|
|
|
