Many businesses keep track on how many hours and minutes their
employees work using Excel.

How much is 5h 49 m + 55m (h = hours, m = minutes)?

If you add 5.49 and 0.55 you get 6.04, which is wrong.

However, if you custom format the cells to [h]:mm and write 5:49 in one
cell, and 0:55 in another, you get the correct answer 6:04. The [h] is
used so the time is not added modulo 24. 20:50 + 10:04 = 06:54 if h:mm
is used, but equals the correct 30:54 with [h]:mm.

If you are lazy, as I proudly am, you don't want to type 05:49 to enter
the time. The 0 shouldn't be needed and the : neither. The answer is to
use the custom format 0":"0#. Now, if you type 5, 0:05 appears.
Likewise, 12 gives 0:12, 549 gives 05:49, 1545 gives 15:45.

Here comes the bad news. You can't give a cell two custom formats at
the same time. In this case you have to pick between [h]:mm and 0":"0#.
If you choose the first the times add up, but you can't be lazy. If you
use the latter the times do not add up, but you can be as lazy as hell.

Allen Wyatt at
http://exceltips.vitalnews.com/Pages...nput_Mask.html
suggests a way out.

'... use another column to show the entered digits converted into a
time. All you need to do is use a formula to do the conversions. For
instance, if the time you entered was in cell A3, you could use the
following formula in a different cell to do the conversion:
=(INT(A3/100)/24)+((A3 - (INT(A3/100)*100))/1440)'

I would type the times lazily with the format 0":"0# and have it
automatically copied to a cell formatted with [h]:mm format I would use
for adding.

If I have hundreds of cells with times to be added, the solution is a
bit laborious, so I looked for something else. This was my idea: Format
all cells with the general format. When a time is entered lazily, like
549, let code convert it to 05:49 and format the cell to [h]:mm. This
is my code:

Dim nrow As Integer
Dim ncol As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If nrow < 0 And ncol < 0 Then
If InStr(Application.Cells(nrow, ncol), ":") = 0 Then
Application.Cells(nrow, ncol) = ftime(Application.Cells(nrow,
ncol))
Selection.NumberFormat = "[h]:mm"
End If
End If
nrow = Application.ActiveCell.Row
ncol = Application.ActiveCell.Column
End Sub

Private Function ftime(t As String) As String
' example: '5' - '00:05'
' example: '12' - '00:12'
' example: '540' - '05:40'
' example: '1123' - '11:23'
t = Right("000" & t, 4)
ftime = Left(t, 2) + ":" + Right(t, 2)
End Function

It works well as long as one stays inside the cells where time is
added. To avoid problems for other cells one might test if the active
cell is in the range of time cells or not.

Do simpler solutions exist?

I am grateful for your view.

Regards,

Jan Nordgreen

http://simpler-solutions.net/pmachin...thinkagain.php
http://myprograms.myblogsite.com/blog

Jan,

You think YOU'RE lazy. I have my big-screen TV on its side so I can lie
down and watch!

One way is to put hours in a column, and minutes in another, as you did, as
plain numbers. Now you don't need to enter all the time-formatting
punctuation, though you do need to press Tab or RightArrow to move across,
from hours to minutes (note that when you then press Enter, it will move you
down and to the left, ready for the next row). Then sum them with something
like:

=(SUM(A2:A10)+SUM(B2:B10)/60)/24 and format that for time.
--
Earl Kiosterud
www.smokeylake.com/

Off-topic: There's a hilarious comedy act by Men in Coats at
http://www.koreus.com/files/200505/men-in-coats.html. You gotta see this.
I had to share with all.

-------------------------------------------

"damezumari" wrote in message
oups.com...
Many businesses keep track on how many hours and minutes their
employees work using Excel.

How much is 5h 49 m + 55m (h = hours, m = minutes)?

If you add 5.49 and 0.55 you get 6.04, which is wrong.

However, if you custom format the cells to [h]:mm and write 5:49 in one
cell, and 0:55 in another, you get the correct answer 6:04. The [h] is
used so the time is not added modulo 24. 20:50 + 10:04 = 06:54 if h:mm
is used, but equals the correct 30:54 with [h]:mm.

If you are lazy, as I proudly am, you don't want to type 05:49 to enter
the time. The 0 shouldn't be needed and the : neither. The answer is to
use the custom format 0":"0#. Now, if you type 5, 0:05 appears.
Likewise, 12 gives 0:12, 549 gives 05:49, 1545 gives 15:45.

Here comes the bad news. You can't give a cell two custom formats at
the same time. In this case you have to pick between [h]:mm and 0":"0#.
If you choose the first the times add up, but you can't be lazy. If you
use the latter the times do not add up, but you can be as lazy as hell.

Allen Wyatt at
http://exceltips.vitalnews.com/Pages...nput_Mask.html
suggests a way out.

'... use another column to show the entered digits converted into a
time. All you need to do is use a formula to do the conversions. For
instance, if the time you entered was in cell A3, you could use the
following formula in a different cell to do the conversion:
=(INT(A3/100)/24)+((A3 - (INT(A3/100)*100))/1440)'

I would type the times lazily with the format 0":"0# and have it
automatically copied to a cell formatted with [h]:mm format I would use
for adding.

If I have hundreds of cells with times to be added, the solution is a
bit laborious, so I looked for something else. This was my idea: Format
all cells with the general format. When a time is entered lazily, like
549, let code convert it to 05:49 and format the cell to [h]:mm. This
is my code:

Dim nrow As Integer
Dim ncol As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If nrow < 0 And ncol < 0 Then
If InStr(Application.Cells(nrow, ncol), ":") = 0 Then
Application.Cells(nrow, ncol) = ftime(Application.Cells(nrow,
ncol))
Selection.NumberFormat = "[h]:mm"
End If
End If
nrow = Application.ActiveCell.Row
ncol = Application.ActiveCell.Column
End Sub

Private Function ftime(t As String) As String
' example: '5' - '00:05'
' example: '12' - '00:12'
' example: '540' - '05:40'
' example: '1123' - '11:23'
t = Right("000" & t, 4)
ftime = Left(t, 2) + ":" + Right(t, 2)
End Function

It works well as long as one stays inside the cells where time is
added. To avoid problems for other cells one might test if the active
cell is in the range of time cells or not.

Do simpler solutions exist?

I am grateful for your view.

Regards,

Jan Nordgreen

http://simpler-solutions.net/pmachin...thinkagain.php
http://myprograms.myblogsite.com/blog

Thanks Earl for your reply. I have to admit that I liked the off-topic
item far better than the on-topic one. :)

I downloaded the video. Hilarious!