XL Hours & minutes calculations

[Aurorasjt]

I want to be able to format cells for hours and minutes only so that (say) 08.20 may be entered and XL will recognise it as 8hrs 20mins and also allow addition of cells for totals that will exceed 24 hours.It seems laborious to have to enter 08:20:00 all the time and especially when seconds are not required.

This is for staff to keep a breakdown of their time for each day of the month and permit a summary for the whole month to be calculated

[jaclaz]

I want to be able to format cells for hours and minutes only so that (say) 08.20 may be entered and XL will recognise it as 8hrs 20mins and also allow addition of cells for totals that will exceed 24 hours.It seems laborious to have to enter 08:20:00 all the time and especially when seconds are not required.

This is for staff to keep a breakdown of their time for each day of the month and permit a summary for the whole month to be calculated

I am not sure that I have understood fully the question , but you cannot simply check cell formats?

Right click on a cell, select Format.

Go to the "hour" in the left "categories", click on any of the formats on the right.

Now click on "Personzlized" on the left.

You should see how the actual format is "conventionally encoded", as an example:

h:mm:ss

• means:
  
• h=hours in a 0÷12 AM/PM format,WITHOUT leading 0 if hour is one digit
  
• :=separated by ":" (colon) from
  
• mm=minutes
  
• :=separated by ":" (colon) from
  
• ss=seconds
  

Now, try formatting column A simply as:

hh:mm

(this shows only hours in a 0÷24 format and minutes).

In first cell of the column (A1) enter only:

"1" and ":" and press [ENTER]

copy the cell down 24 times, up to cell A25.

In cell B1 insert formula =A1

In cell C1 insert formula =SUM(A$1:A1)

In cell D1 insert formula =C1

Copy cells B1:D1 downwards up to row 25

Copy column D rightwards a few times, say in column E,F,G,H.

Now select colums B and set it's format to "General"

Select columns D,E,F,G and H and set their format as "General"

You should have an easy representation of how "hours" are stored in Excel.

Now, play with different settings for the cell format of columns D,E,F,G.

For column H, change the format to:

dd/mm/yyyy hh:mm:ss

So, what you entered initially as "1:" has become 01:00 but actually represents one in the morning of "day 0" of January 1900. (some spreadsheet programs may actually use 31/12/1899 )

As you can see in line 24, when you sum 24 times "one hour", you get "00:00" of the "day after".

Now, in cell I1 insert function:

=HOUR(H1-H$1)

and copy it down up to cell I24

Now, in cell J1 insert function:

=HOUR(A1)

and copy it down up to cell J23

In cell J25 insert function:

=SUM(J1:J24)

Now, in cell K1 insert function:

=MINUTE(A1)

and copy it down up to cell K23

In cell K25 insert function:

=SUM(K1:K24)

Play a bit changing values in cells A1 to A24.

So, summing "hours" increases the actual hours up to 24 and then it increases the day (or date), in other words a spreadsheet treats "hours" exactly as a conventional clock does, using a 12 or 24 modulus.

Data input as "hours" is actually a date (01/01/1900) of which you can ONLY see the hour part.

You need "special" DATE functions, like HOUR, MINUTES, SECONDS to calculate the difference between two dates OR extract the relevant parts.

I hope this clears a bit the issues....

jaclaz