Depends on if the decimal is decimal time like 2:30 would be the equivalent
of decimal 2.5 or if it is
a representation of time meaning that 2:30 = 2.30. If the latter you can
convert it like

=INT(A1)/24+MOD(A1,1)/14.4

or

=--TEXT(100*A1,"00\:00")


if the former

=A1/24

format all results as [h]:mm

--


Regards,


Peo Sjoblom

"Karen" wrote in message
...
Thanks for the clarification. I knew it was time to go to bed and forget
it.
My problem, however, has not gone away. The destination spreadsheet (not
created by me) is huge and every cell is in 2 decimal number format. How
do I
deal with that when the payroll export is formatted using a colon? This
company just started using this new payroll system and I have nothing to
show
me how the old system reported the numbers. Up to now they were all input
manually but I am supposed to link the two.
--
Excelsolutions4U


"David Biddulph" wrote:

You are getting yourself progressively more confused, Karen. You *must*
be
more careful in distinguishing numbers with decimals on one hand and
Excel
times in hours and minutes on the other hand.

The things which would add up to 29.46 would be 7.45, 7.45, 8.44, and
6.12.
These are numbers with decimals. Note the decimal point, not the
semi-colon
which is used to separate hours from minutes in an Excel time format.

If you have 7:45, 7:45, 8:44, and 6:12 as times, these add up to 30:26,
in
other words 30 hours and 26 minutes.
What you are seeing in your 3229167+.2583333+.363889+.3229167 is Excel's
internal storage of these times, which is in units of a day. Your answer
of
1.27 is again in units of a day shown as a decimal number. If you format
that cell as [h]:mm it again shows 30:26.

Dividing 30:26 (a time) by 24 does *not* give 1.26083333. It gives 1:16
(in
other words 1 hour and 16 minutes).
Dividing 30.26 (a decimal number) by 24 *does* give 1.26083333, but of
course this is totally irrelevant to the question as 30.26 is not the
same
as 30:26, and if you wanted to convert 30:26 to decimal hours you would
*multiply* by 24, not divide, and on formatting the result as General or
a
Number you would see 30.43333, which is the decimal number of hours
equivalent to your time of 30 hours and 26 minutes.

Is that starting to get clearer?
--
David Biddulph

"Karen" wrote in message
...
No, they add up to 29.46, not the 30:26.

When I evaluate the formula, the last step shows
+.3229167+.2583333+.363889+.3229167 as the figures used. although I
have
shortened them a few digits, adding them does reflect the 1.27 in the
[h]:mm
format

I divided the 30:26 by 24 & get 1.26083333, so that's a beginning. Is
it
possible that three quarters of an hour difference (30.26-29.46) is due
to
rounding?

Excelsolutions4U


"daddylonglegs" wrote:

Presumably you mean that [h]:mm shows 30:26. Isn't that the correct
sum
for
the times you have?

"Karen" wrote:

O& gets same results as [h]:mm format except with a decimal. The
numbers are
7:45, 7:45, 8:44, and 6:12. simply added they equal 29.46. Number
format
shows 30:26 and [h]:mm shows 1.26, neither of which I know how to
tie
to the
actual hours and minutes it should reflect.
--
Excelsolutions4U


"daddylonglegs" wrote:

Hello Karen,

What does the hours:minutes column look like? If there are no
hours
do you
get 0:45 or does it display like :45? If it's the latter try

:=SUMPRODUCT((0&'[Kronos Daily Labor
Report]2'!$O$15:$O$365)*('[Kronos Daily Labor
Report]2'!$I$15:$I$365={100,130}))

If you can get the calculation to work then you can format the
result
cell
as [h]:mm to display hours over 23:59

"Karen" wrote:

I have to use an hours:minutes column from a Kronos payroll
report(exported
to Excel) in the following formula:=SUMPRODUCT(('[Kronos Daily
Labor
Report]2'!$O$15:$O$365)*('[Kronos Daily Labor
Report]2'!$I$15:$I$365=100)+('[Kronos Daily Labor Report
.xls]2'!$I$15:$I$365=130))

(Sum column O if the position code is 100 or 130)
This formula is on an external spreadsheet.

I am getting a value error and my first problem is to determine
the
formatting in the h:mm column coming from the payroll report. I
have followed
previous threads, copied blank cells and pasted the formatting,
etc. all to
no avail. I did something to make it work yesterday, but can't
do
it again.
Converting this column will have to be part of a macro.

Second problem I have is when the h:mm are added, the formatting
is
to be
hours:minutes without changing to days after reaching the
23:59:59
amount.
Will this formatting be on the external spreadsheet containing
the
formula
or can I fix it all on the payroll sheet with the macro?

If someone will look at these sheets, I can email them. Although
I
am using
Vista and Office 2007, the user will have XP and Office 2003.
Thank
You!
Excelsolutions4U