SUMPRODUCT formula using hours:minutes

[Karen]

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

 

[Peo Sjoblom]

The values errors derive from text being calculated so before anything else
you need to make sure the data is not text.
Many times when importing what looks like numbers from mainframes and other
programs Excel sees them as text and regardless if you change the format in
the cell after the import is done it will stay that way. Note that in your
case the text error comes from column O

Test the values with


=ISTEXT('[Kronos Daily Labor Report]2'!$O$15)

copy down

if you get TRUE anywhere it is text

If so post back

Also the formula can be simplified to

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

That will not stop the values errors though

Try to select the import in column O, then do data>text to column and click
finish
and see if the value error disappears


Finally format the result as [hh]:mm:ss, that way it won't spill over to
days etc





--


Regards,


Peo Sjoblom

 

[daddylonglegs]

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]

Thanks for the prompt reply. Although the column was formatted correctly,
beyond the visible screen a single column heading had pasted there when I did
a sort. Thanks to your search formula I removed the one cell of text and the
formula now works. I also appreciate the simplified syntax! Now I am about to
try the solution to the h:m formatting.
--
Excelsolutions4U

The values errors derive from text being calculated so before anything else
you need to make sure the data is not text.
Many times when importing what looks like numbers from mainframes and other
programs Excel sees them as text and regardless if you change the format in
the cell after the import is done it will stay that way. Note that in your
case the text error comes from column O

Test the values with


=ISTEXT('[Kronos Daily Labor Report]2'!$O$15)

copy down

if you get TRUE anywhere it is text

If so post back

Also the formula can be simplified to

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

That will not stop the values errors though

Try to select the import in column O, then do data>text to column and click
finish
and see if the value error disappears


Finally format the result as [hh]:mm:ss, that way it won't spill over to
days etc





--


Regards,


Peo Sjoblom

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

 

[Karen]

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

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

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

 

[Karen]

I have a question on your revised formula. I thought with using those { }
brackets, it would require a control shift enter for an array, but I typed
them in and it worked by just entering. Could you explain? Thanks!
--
Excelsolutions4U

The values errors derive from text being calculated so before anything else
you need to make sure the data is not text.
Many times when importing what looks like numbers from mainframes and other
programs Excel sees them as text and regardless if you change the format in
the cell after the import is done it will stay that way. Note that in your
case the text error comes from column O

Test the values with


=ISTEXT('[Kronos Daily Labor Report]2'!$O$15)

copy down

if you get TRUE anywhere it is text

If so post back

Also the formula can be simplified to

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

That will not stop the values errors though

Try to select the import in column O, then do data>text to column and click
finish
and see if the value error disappears


Finally format the result as [hh]:mm:ss, that way it won't spill over to
days etc





--


Regards,


Peo Sjoblom

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

 

[daddylonglegs]

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

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

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

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

 

[Peo Sjoblom]

It is because they are wrapped inside SUMPRODUCT.
SUMPRODUCT the way used by you is an array formula without the need for
array entering

--


Regards,


Peo Sjoblom

I have a question on your revised formula. I thought with using those { }
brackets, it would require a control shift enter for an array, but I typed
them in and it worked by just entering. Could you explain? Thanks!
--
Excelsolutions4U

The values errors derive from text being calculated so before anything
else
you need to make sure the data is not text.
Many times when importing what looks like numbers from mainframes and
other
programs Excel sees them as text and regardless if you change the format
in
the cell after the import is done it will stay that way. Note that in
your
case the text error comes from column O

Test the values with


=ISTEXT('[Kronos Daily Labor Report]2'!$O$15)

copy down

if you get TRUE anywhere it is text

If so post back

Also the formula can be simplified to

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

That will not stop the values errors though

Try to select the import in column O, then do data>text to column and
click
finish
and see if the value error disappears


Finally format the result as [hh]:mm:ss, that way it won't spill over to
days etc





--


Regards,


Peo Sjoblom

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

 

[Karen]

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

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

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

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

:

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

 

[David Biddulph]

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

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

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

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


:

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

:

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

 

[Karen]

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

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

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

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

:

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


:

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

:

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

 

[Karen]

I can get the 30.43 by formatting as you have directed, converting the colon
separater to a decimal as desired in the destination spreadsheet, so that
should solve my problem and return the correct answer. Thanks!
Excelsolutions4U

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

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

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

:

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


:

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

:

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

 

[Peo Sjoblom]

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

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

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

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


:

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

:

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


:

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

:

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