Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Coverting time
I guess I am not being clear as to what I want to do. I take each card and
enter the times in and out into excel for the whole week for each day. I need each cell that I enter the time into to be converted to military time for the hours and the minutes are divided by 60 to get the # to add/subract. So I would enter in @ 6:59am out for lunch 12:00pm back @12:30pm out @ 4:25pm. I need the time converted to look like this in @ 6.98 out @12.00 back @ 12.50 out at 16.42 to get total hours of 8.94. That is adding 12 to pm hours and dividing all minutes by 60 (actual minutes in an hour). I want to be able to enter using a decimal instead of a colon in the time. What I do now is take the card and change each punch by dividing the minutes by 60 by hand and enter the result in the spreadsheet. I am trying to eliminate having to do the division by hand and then enter to excel. I am hoping to enter a formula in the next cell that converts 12:30 to 12.50 and 4:25 to 4.50 (notice the colon and decimal). Is this possible -- Kim -- Kim |
#2
|
|||
|
|||
What you want is fairly simple. Where A2 is your time:
= A2 * 24 Format the result as a number with decimals, not as Date or Time Results: 12:30 am = 0.50 4:25 am = 4.42 (not 4.5) 12:30 pm = 12.5 4:25 pm = 16.42 FYI: Excel stores time as a decimal value (where 1 = 1 day) 12:00 pm (noon) = 1/2 day = 0.50 1 hour = 1/24 = 0.041666667 (aka 1:00 am) 1 minute = 1/24/60 = 0.0006944444 (approx) (aka 12:01 am) HTH, -- George Nicholson Remove 'Junk' from return address. "Kim" wrote in message ... I guess I am not being clear as to what I want to do. I take each card and enter the times in and out into excel for the whole week for each day. I need each cell that I enter the time into to be converted to military time for the hours and the minutes are divided by 60 to get the # to add/subract. So I would enter in @ 6:59am out for lunch 12:00pm back @12:30pm out @ 4:25pm. I need the time converted to look like this in @ 6.98 out @12.00 back @ 12.50 out at 16.42 to get total hours of 8.94. That is adding 12 to pm hours and dividing all minutes by 60 (actual minutes in an hour). I want to be able to enter using a decimal instead of a colon in the time. What I do now is take the card and change each punch by dividing the minutes by 60 by hand and enter the result in the spreadsheet. I am trying to eliminate having to do the division by hand and then enter to excel. I am hoping to enter a formula in the next cell that converts 12:30 to 12.50 and 4:25 to 4.50 (notice the colon and decimal). Is this possible -- Kim -- Kim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Time Manipulation | Excel Discussion (Misc queries) | |||
conditional formatting with time values | Excel Discussion (Misc queries) | |||
Time Sheets | New Users to Excel | |||
Time Sheets | Excel Discussion (Misc queries) | |||
unmet challenge | Excel Worksheet Functions |