Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
I have some timed data to enter. Each entry has two values: (1) number
of repetitions and (2) elapsed time in minutes and seconds (mm:ss). Some sample data is: Reps mm:ss 25 3:22 202 15:00 99 11:30 1405 77:22 I want to enter the data just as shown above. Note that the minutes may exceed 59. That is, time over an hour is not shown as hh:mm:ss. I want Excel to calculate the number of repetitions/hour. Reps mm:ss rph 25 3:22 446 202 15:00 808 99 11:30 517 1405 77:22 1090 The calculations are working correctly, but I am having trouble with the data entry. 1. If I enter "3:22", it goes in as "03:22:00" = 3 hours and 22 minutes, not 3 minutes and 22 seconds. I can enter "0:03:22" and it works, but I'd prefer not to have to enter the hours. Is there a way for me to enter "3:22" and have it go it as 3 minutes and 22 seconds? 2. All times are in minutes and seconds, even though the total time may be over an hour. If I enter the last entry as "00:77:22", it takes the correct value, but it changes the cell formatting "General". If I change it back to "mm:ss", I get "17:22". Assuming I get an answer to #1 and can enter "77:22", is there a way for me to format it to display as "77:22"? Thanks -- Running Excel 2000 SP-3 on Windows 2000 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
One way:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If InStr(.NumberFormat, ":") Then On Error Resume Next Application.EnableEvents = False .Value = .Value / 60 Application.EnableEvents = True On Error GoTo 0 .NumberFormat = "[m]:ss" End If End With End Sub In article , LurfysMa wrote: I have some timed data to enter. Each entry has two values: (1) number of repetitions and (2) elapsed time in minutes and seconds (mm:ss). Some sample data is: Reps mm:ss 25 3:22 202 15:00 99 11:30 1405 77:22 I want to enter the data just as shown above. Note that the minutes may exceed 59. That is, time over an hour is not shown as hh:mm:ss. I want Excel to calculate the number of repetitions/hour. Reps mm:ss rph 25 3:22 446 202 15:00 808 99 11:30 517 1405 77:22 1090 The calculations are working correctly, but I am having trouble with the data entry. 1. If I enter "3:22", it goes in as "03:22:00" = 3 hours and 22 minutes, not 3 minutes and 22 seconds. I can enter "0:03:22" and it works, but I'd prefer not to have to enter the hours. Is there a way for me to enter "3:22" and have it go it as 3 minutes and 22 seconds? 2. All times are in minutes and seconds, even though the total time may be over an hour. If I enter the last entry as "00:77:22", it takes the correct value, but it changes the cell formatting "General". If I change it back to "mm:ss", I get "17:22". Assuming I get an answer to #1 and can enter "77:22", is there a way for me to format it to display as "77:22"? Thanks -- Running Excel 2000 SP-3 on Windows 2000 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
On Sat, 24 Feb 2007 09:37:25 -0700, JE McGimpsey
wrote: One way: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If InStr(.NumberFormat, ":") Then On Error Resume Next Application.EnableEvents = False .Value = .Value / 60 Application.EnableEvents = True On Error GoTo 0 .NumberFormat = "[m]:ss" End If End With End Sub Thanks for the macro code. I was afraid that that was what I would have to do. So there's no built-in formatting codes to do whatr I want? Thanks for the code. I'll try it out. At least then I'll have total control. ;-) -- Running Excel 2000 SP-3 on Windows 2000 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
In article ,
LurfysMa wrote: So there's no built-in formatting codes to do whatr I want? No - formatting does *nothing* to change how inputs are parsed (except that setting format to Text bypasses the parser entirely). |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
Preformat cells as Text in Column A
Create a helper Column B In B1: =IF(A1="","",TIME(0,LEFT(A1,FIND(":",A1)-1),RIGHT(A1,FIND(":",A1)-1))) Format cell as [m]:ss "LurfysMa" wrote: I have some timed data to enter. Each entry has two values: (1) number of repetitions and (2) elapsed time in minutes and seconds (mm:ss). Some sample data is: Reps mm:ss 25 3:22 202 15:00 99 11:30 1405 77:22 I want to enter the data just as shown above. Note that the minutes may exceed 59. That is, time over an hour is not shown as hh:mm:ss. I want Excel to calculate the number of repetitions/hour. Reps mm:ss rph 25 3:22 446 202 15:00 808 99 11:30 517 1405 77:22 1090 The calculations are working correctly, but I am having trouble with the data entry. 1. If I enter "3:22", it goes in as "03:22:00" = 3 hours and 22 minutes, not 3 minutes and 22 seconds. I can enter "0:03:22" and it works, but I'd prefer not to have to enter the hours. Is there a way for me to enter "3:22" and have it go it as 3 minutes and 22 seconds? 2. All times are in minutes and seconds, even though the total time may be over an hour. If I enter the last entry as "00:77:22", it takes the correct value, but it changes the cell formatting "General". If I change it back to "mm:ss", I get "17:22". Assuming I get an answer to #1 and can enter "77:22", is there a way for me to format it to display as "77:22"? Thanks -- Running Excel 2000 SP-3 on Windows 2000 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
Does it matter if the time is entered as hours? If you format cells as [h]:mm
and enter 77:22 then it will look exactly as you want and you can adjust the formula in your rph column (multiply by 60) to get the answer you require. "JE McGimpsey" wrote: In article , LurfysMa wrote: So there's no built-in formatting codes to do whatr I want? No - formatting does *nothing* to change how inputs are parsed (except that setting format to Text bypasses the parser entirely). |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
On Sat, 24 Feb 2007 12:32:08 -0800, Teethless mama
wrote: Preformat cells as Text in Column A Create a helper Column B In B1: =IF(A1="","",TIME(0,LEFT(A1,FIND(":",A1)-1),RIGHT(A1,FIND(":",A1)-1))) Format cell as [m]:ss Why does that custom formatting work? I thought the brackets were for conditional formatting? Where is this documented? It turns out that "[s]" will show total seconds, too. So many hidden goodies in Excel. It's like a scavenger hunt. -- Running Excel 2000 SP-3 on Windows 2000 |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
On Sat, 24 Feb 2007 13:30:03 -0800, daddylonglegs
wrote: Does it matter if the time is entered as hours? If you format cells as [h]:mm and enter 77:22 then it will look exactly as you want and you can adjust the formula in your rph column (multiply by 60) to get the answer you require. Now that's a clever solution. I was tempted by it, but I bet I would forget that I was using nn:nn as mm:ss even though Excel considers it hh:mm. I am impressed by the outside-the-box thinking, though. -- Running Excel 2000 SP-3 on Windows 2000 |
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Showing time as minutes
In article ,
LurfysMa wrote: Why does that custom formatting work? I thought the brackets were for conditional formatting? Brackets can be used in custom (not conditional) formats, e.g., [Red][<-100]-0;[Yellow][<0]0;[Green]0;@ Where is this documented? XL Help "About custom number formats" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time in Minutes | Excel Worksheet Functions | |||
calculating timesheet, time-in/time-out = total hours & minutes, . | Excel Worksheet Functions | |||
Converting time formats into actual time(minutes) | Excel Discussion (Misc queries) | |||
Using military time but with 100 minutes not 60 minutes | Excel Discussion (Misc queries) | |||
Time Calculation in Minutes only ? | Excel Worksheet Functions |