Decimal time

[Kim]

I need to convert regular time to decimal time. example regular time in A1
is 1:38 I need that number to be converted to decimal time = to 13:63. add
12 to the hour and divide 38 into 60 to get the minutes. Our time clock
prints on a 12 hour basis and I enter to excel in decimal time. Any
suggestions?

 

[David Billigmeier]

Assume your time is in A1, enter this formula in an empty cell and format as
number:
=HOUR(A1)+MINUTE(A1)/60

 

[Kim]

Thank you so much!!! I have been going crazy all morning trying to figure
that out!! I did change it a bit and after the (HOUR) A1 i had to put in +12
to get the 13. However, this was great!! Now one more question......
is there any way to do this without using a colon ) in the reg time cell?
Can I use a decimal instead and what would that formula be? Thank you again

 

[Bob Phillips]

=A2*24

what about 1:38 in the morning, how should that show?

 

[David Billigmeier]

Do you mean you have a time stamp formatted as 1.38 AM instead of 1:38 AM?

 

[Kim]

1:38 would be 1(am) 63. 1:63. The hour would be the same as it is Military
Time and the minutes get converted by dividing by 60. So am stays the same
pm hours have 12 added to it. Can I use a decimal instead of a colon for the
time in A1? Thank you

 

[Kim]

I tried the formula =A1*24 and it did convert the minutes to the 63 but left
the hours as 1 instead of 13. What next? Thank you for your help

 

[Myrna Larson]

Sounds to me as though your entries are in fact valid Excel times, in which
case all you need is to format the cell with a format that shows hours on a
24-hour clock rather than AM/PM. In the Format/Cells/Number/Time list, pick
the entry that shows the hours greater than 12.

 

[Kim]

My spreadsheet is set up like this: E7 is in time D7 is out time E6 is in
time and D6 is out time. The formula is E7-D7+E6-D6 to get the total hours
worked. Our time cards are printed in regular format. I need to convert
this regular time to decimal/military time to get total hours worked. I am
looking for a formula to change the hours and minutes to decimal/military
time. 1.38 = 13.63. hours +12 and minutes /60. Can I enter the hours with
decimals instead of colons and convert to decimal/military time? I am sure I
am way overthinking this and making it more difficlut than it needs to be.
Any help is sure appreciated.

 

[Myrna Larson]

Excel times are stored as a fraction of a day. To convert that to hours, you
just multiply by 24. So, if you want the result in decimal hours, change the
formula to

=(E7-D7+E6-D6)*24

and format the cell as General. Or leave the formula as-is, and in the cell
where you calculate the pay using an hourly rate, multiply by 24 in that
formula. i.e. if the formula in F7 is =E7-D7+E6-D6, and you calculate the pay
in G7, the formula for G7 is =F7*24*<hourly pay rate>

 

[Kim]

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 / 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 hope this helps. Thank
you all.

 

[Myrna Larson]

I would enter the times with a decimal point instead of a colon, i.e. 6.59 am
or 6.59 a (note the space before am or a -- that's important).

Then when you've finished all of the entries, used Edit/Replace and replace
the period with a colon. Now Excel will automatically convert your entries
text entries to numeric times (as I said, a fraction of a day) and you can do
the math as I've described before. You don't need to convert the times to
decimal hours to calculate pay. Just calculate the difference, leave it as a
time (using a time format like hh:mm or [hh]:mm, so you'll see hours and
minutes -- 8:56 not 8.94). In the cell where you multiply that time value by
an hourly pay rate, also multiply the result by 24.

 

[Myrna Larson]

Note that if you enter hours after noon using the 24-hour clock, i.e. 13.30
rather than 1.30, you can skip the p/pm, which should also speed up the data
entry.

I would enter the times with a decimal point instead of a colon, i.e. 6.59 am
or 6.59 a (note the space before am or a -- that's important).

Then when you've finished all of the entries, used Edit/Replace and replace
the period with a colon. Now Excel will automatically convert your entries
text entries to numeric times (as I said, a fraction of a day) and you can do
the math as I've described before. You don't need to convert the times to
decimal hours to calculate pay. Just calculate the difference, leave it as a
time (using a time format like hh:mm or [hh]:mm, so you'll see hours and
minutes -- 8:56 not 8.94). In the cell where you multiply that time value by
an hourly pay rate, also multiply the result by 24.