time issue

[toffa]

Hi there have a small time calcution issue

I have a time sheet that i enter time of completed task ie when started and when finished so i get a figure at the end
eg
A1 B1 C1
12.30 13.30 1.00
15.45 15.55 .10


total is 1.10 or 1 hour 10 minutes what i want is a figure that is in decimal so i can charge it 1.10 would become 1.17

is this an easy calculation that i can perfrom or is it a two or three step process..


Chris

 

[kpetursson]

Why don't you format the cells as Time. And input you values as such ie 12:30 not 12.30

see

http://www.tek-tips.com/viewthread.cfm?qid=1004422

for something similar


Kevin Petursson

 

[toffa]

the entered times isnt the problem it is when i have created a sum of all the times i want to charge from that figure but if the time figure is 1 hour 15 minutes it shows 1.15 or 1:15 but i need to charge 1.25 does this make sence.

Chris

 

[AnotherHiggins]

Do you mean that you want to round to the nearest 1/4 hour?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[bartsimpson]

You could enter the date as anotherhiggins suggests ( say in A1), and then enter the following code to convert it to decimal :

=HOUR(A1)+(MINUTE(A1)/60)

Remember to format the cell containing the formula to be number format.

 

[asrisk]

In my own timesheet, I use the ROUND function, like this (with C1 formatted as a number, and A1/B1 containing the start and stop times (formatted as hh:mm):

Range("C1") = WorksheetFunction.Round((Range("B1") - Range("A1"))* 24, 1)

I round to a tenth of an hour (one decimal place). For two decimal places, replace the final 1 with a 2.

-------
The joke cannot be found
[sup]The funny quote you are looking for might have been removed, had its name changed, or is temporarily unavailable.[/sup]

 

[AnotherHiggins]

toffa,

Sorry, I just realized what it is you're trying to accomplish (your first post was the key - representing 10 minutes as .17).

First, I assume you are using Excel, right?

I'm afraid you're mistaken when you say "the entered times isnt the problem". That is precisely the problem. You don't have times entered, you have decimals! That is Very different.

You see, every date in Excel is stored as a whole number. Today (Feb 22, 2005) is stored in Excel as 38405 - that's 38,405 days since 1/1/1900. Times in Excel are stored as decimals, portions of a day. 6 AM is .25, 12 noon is .5, 6 PM is .75, 5:13 is 0.2173611, etc.

Excel cannot see what you have as times. You must change how it is input if you want to perform time calculations with your data. The easiest way will be to use Find and Replace ([Ctrl]+[H]). Find decimal and replace with colon.

Warning: depending on how your data is currently formatted (number vs. text) you might have problem like 7.10 turning into 7:01. If you run into something like that, post back.

Good luck.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[toffa]

Ok thanks for your help i was able to succesfully complete the calculation by using =HOUR(A1)+(MINUTE(A1)/60)
Chris

 

[kpetursson]

toffa

I'm glad you've got it figured out, because I'm just confused with you anwser.

What happen if you have 1.50 and 1.50 to add to gether.
If excel adds these as decimal you get 3.00, but If I understand you correctly 1.50 represents 1 hour and 50 minutes. 1:50+1:50 is 3 hours and 40 Minutes, not the 3.00

I think if you want to do calculations with time, then the data entered should be formatted as time.

Hope it all works out for you.

Kevin Petursson

 

[toffa]

the seperator is . and the format is [h].mm

sorry i should hae posted that.

Chris

 

[ElPatNot]

The formula I have always used has been: [h]:mm:ss. You can also use h:mm:ss, but if you go above 24 hours, Excel will freak out and do some random things with your cells.

El Patrick of Not
Punk at heart, Sys Admin by trade
(Finally lost It)

 

[AnotherHiggins]

the seperator is . and the format is [h].mm

Interesting. What happens if you type 6.50 into a cell? For me, it is converted to 156.00 (just as it would be converted to 156:00 if the cell was formatted [h]:mm - that's 6 days plus 12 hours => 6*24=144 hrs + 12 hrs => 156 hrs). So are you still entering times using a colon so that Excel recognized it as a date?

A warning on using [COLOR=blue white]=HOUR(A1)+(MINUTE(A1)/60)[/color] - if the value is over 24 hours (30 hours for example) this formula will give you 6 hours! A better way to proceed is to use [COLOR=blue white]=A1*24[/color] formatted as a number. This will always work, even for values over 24 hours.

Excel will freak out and do some random things with your cells

There's nothing random about it. If your time goes over 24 hours, Excel 'wraps around' at 24 hours and displays the remainder over the next smallest multiple of 24. Example: 34:15:25 in a cell formatted as h:mm:ss will display as 10:15:25 (10+24=34). Now format that same cell as [h]:mm:ss and Excel will display 34:15:25.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886