Adding a range of hours and minutes

[kets23]

Hi

I have a large data sheet showing various hours and minutes along with other data , I have summarised via a pivot table but have noticed that the hours and minutes are not being calculated correctly.
For example, from the pivot table:

- The total hours and minutes on the attached for CC units under Year 1 shows as 1497 when the correct hours/minutes should be 148 hours and 20 minutes
- The total hours and minutes on the attached for NC units under Year 1 shows as 1835.60 when the correct hours/minutes should be 1836.50

Also from the pivot table,, under NC units for Year 2, this shows as 1167.90 when this should be 1168 hours

Data for all the hours is from the confirmed hours column, I need to obtain similar data for Unconfirmed hours and total hours

Any ideas on how I can get an accurate analysis of all the units total hours and minutes in the correct format for year 1 & year 2 that I can then analyse by host and type.

Also when I try to sum up the hours total in HHMM column, this is not showing me totals when I select a certain range

Thanks in advance

Kets

 

[6StringJazzer]

Using hhh.mm to express time is unconventional and you can't do arithmetic with it (all of the columns except "Hours Total in HHMM"). For your example for CC Year 1, the sum of those numbers is 1497.2 (not 1498.2). And that's not 1497 hours and 20 minutes, its 1497.2 hours (1497 hours and 12 minutes). Your display format probably does not show decimal points so 1497 is correct. You did not show us data for NC so I can't check that one.

Also when I try to sum up the hours total in HHMM column, this is not showing me totals when I select a certain range

I'm not clear on what you mean by that. What is not showing you totals? Do you mean in the status bar? It should work if the data is numeric. Is that column time values, or is it something formatted as a text string?

What you perceive as a problem is most likely in how you think the data should work vs. how it really works. So it is essential to see your actual data.

Unfortunately we can't tell anything by looking at a picture. Can you paste in the actual data? Consider using the XL2BB add-in. Better yet provide a link to the file from a cloud file service (Dropbox, Google Docs, OneDrive, etc.)

 

[kets23]

Hi

Data pasted

Sum of Confirmed Hours	Yead
Trust	YEAR 1	YEAR 2	(blank)	Grand Total
CC Units	1497	987.8	2484.8
DD units	640	1324.3	1964.3
GG Units	348.45	348.45
NA Units	751.75	751.75
NC Units	1835.6	1167.9	3003.5
NH Units	5474.3	5260.55	10734.85
NU Units	35705.25	36749.35	72454.6
SF Units	8640.6	6472.86	15113.46
UHDB Units	14014.73	5500.15	19514.88
(blank)
Grand Total	67807.48	58563.11	126370.59

 

[kets23]

Hi

Data pasted

Sum of Confirmed Hours	Yead
Trust	YEAR 1	YEAR 2	(blank)	Grand Total
CC Units	1497	987.8	2484.8
DD units	640	1324.3	1964.3
GG Units	348.45	348.45
NA Units	751.75	751.75
NC Units	1835.6	1167.9	3003.5
NH Units	5474.3	5260.55	10734.85
NU Units	35705.25	36749.35	72454.6
SF Units	8640.6	6472.86	15113.46
UHDB Units	14014.73	5500.15	19514.88
(blank)
Grand Total	67807.48	58563.11	126370.59

 

[kets23]

Units	Host	Type	Year	Confirmed Hours	Unconfirmed Hours	Total (confirmed - unconfirmed)	Hours Total in HHMM
CC Units	Aea 1	MW	YEAR 1	0.00	0.00	0.00	0:00
CC Units	Aea 1	MW	YEAR 1	0.00	0.00	0.00	0:00
CC Units	Aea 1	MW	YEAR 1	0.00	0.00	0.00	0:00
CC Units	Aea 1	MW	YEAR 1	0.00	0.00	0.00	0:00
CC Units	Aea 1	MW	YEAR 1	0.00	0.00	0.00	0:00
CC Units	Area 2	MW	YEAR 1	137.30	0.00	137.30	137:30
CC Units	Area 2	MW	YEAR 1	112.30	0.00	112.30	112:30
CC Units	Area 2	MW	YEAR 1	73.00	0.00	73.00	73:00
CC Units	Area 3	MW	YEAR 1	106.50	0.00	106.50	106:50
CC Units	Area 3	MW	YEAR 1	99.00	0.00	99.00	99:00
CC Units	Area 3	MW	YEAR 1	108.00	0.00	108.00	108:00
CC Units	Area 4	MW	YEAR 1	123.15	0.00	123.15	123:15
CC Units	Area 4	MW	YEAR 1	123.15	0.00	123.15	123:15
CC Units	Area 4	MW	YEAR 1	112.30	0.00	112.30	112:30
CC Units	Area 4	MW	YEAR 1	112.30	0.00	112.30	112:30
CC Units	Area 4	MW	YEAR 1	195.00	0.00	195.00	195:00
CC Units	Area 4	MW	YEAR 1	195.00	0.00	195.00	195:00

 

[kets23]

Just to clarify, the issue is with the hours and minutes as shown in the data below, year 2 is an example of data showing 1773.9 and 3600.9 when this is not correct as the minutes should be showing within 60 minutes

YEAR 1	YEAR 2	Grand Total
187.3	187.3
2109.3	1773.9	3883.2
348.45	348.45
109	109
1876	3600.9	5476.9
5055.4	2949.85	8005.25

 

[6StringJazzer]

I am not following what you're doing. If 1773.9 is in units of hours, then it's 1773 hours and 54 minutes. I do not understand what you mean by "the minutes should be showing within 60 minutes". And the column Total has decimal hours, and the column Hours Total has the same digits in a different format, which doesn't make sense.

Which cells have data, and which cells have formulas? For cells with formulas, what are the formulas?

 

[kets23]

Yes 1773.9 is in units of hours but need the pivot table to show this as 1773 hours and 54 minutes (this is what I meant in my previous message), this is what I need to display for all others in the unit of hours.

Thanks

 

[6StringJazzer]

If you have a value like 17743.9 that is in units of hours in, for example, A1, then you need another column to convert it to a time value:

=A1/24

Then you can display the data from the new column in the pivot table as h:mm.

It seems like maybe that is what you are trying to do in your last two columns but I can't figure out based on what I've seen so far.

 

[kets23]

Thank you