Formatting for hours and minutes, etc.

[hollyequine]

Hi there, I am data dumping from our timekeeping system into Excel 2003. I have associates who worked 41 hours and 55 minutes (41.55) and some that worked 22 hours and 10 minutes (22.10).

Question 1:
How do I format so Excel reads this as 41:55 and 22:10, respectively (NOT in minutes and seconds, I need hours and minutes...)

Question 2:
I need to add these two hour sections together and have Excel recognize the TOTAL time as 64:05 NOT 63.65)

Help please!!

 

[Gerald Higgins]

Hi, welcome to the board.
Use the [h]:mm format.

 

[rs2k]

Hi There,

Custom format [h]:mm for the times.
Then add the 2 cells together (=SUM(A1:A2), A1+A2 etc... depends where your cells are) using the same custom format.


HTH
Colin

 

[lenze]

With out having to change the Format, you can use

=DOLLARFR(DOLLARDE(SUM(A2:A3),60),60)

which in your example would display as
64.05. Not sure what you really want!! There may be other ways.
lenze

 

[hollyequine]

Thank you, but I only have the [h]mm:ss format option under special, and I don't want seconds...is this because I am in Excel 2003 vs. 2007

This isn't the end of the world, I am just wondering why I don't have that option and it is the one I need! I believe I can add the times once I have them formatted correctly.

 

[Gerald Higgins]

Use a custom format. Just type in the format you want. This works in 2003.

 

[hollyequine]

Sorry, big problems, when I format cells as [h]:mm:ss and type in 41.55 in the cell turns into 2/10/1900 1:12:00 PM into my formula bar and <TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=98 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584; mso-outline-level: 1" width=98><TBODY><TR style="HEIGHT: 22.5pt; mso-height-source: userset" height=30><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 74pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 22.5pt; BACKGROUND-COLOR: transparent" width=98 height=30 x:num="41.55">997:12:00</TD></TR></TBODY></TABLE> in my cell

????????? AGH!

 

[Norie]

What are the actual values that you are getting from this 'data dumping?

I don't see how you would be ending up with seconds.

 

[barry houdini]

If you already have the times imported as 41.55 then you won't be able to simply reformat (as you found out).

If your "times" are in A2 down then try using a formula in column B to get the values you want, i.e. in B2

=TEXT(A2*100,"00\:00")+0

Now custom format B2 as [h]:mm and you should see the correct time value

Copy down column and then you can do any calculations you want using column B values

 

[hollyequine]

If you already have the times imported as 41.55 then you won't be able to simply reformat (as you found out).

If your "times" are in A2 down then try using a formula in column B to get the values you want, i.e. in B2

=TEXT(A2*100,"00\:00")+0

Now custom format B2 as [h]:mm and you should see the correct time value

Copy down column and then you can do any calculations you want using column B values

Thank you Barry!! This worked! Thanks everyone else, what a GREAT resourceful group you are!!!