MartinS13X
New Member
- Joined
- Apr 4, 2018
- Messages
- 17
- Office Version
- 2019
- Platform
- Windows
Dear MrExcel experts,
I'm trying to save my girlfriend, who is a manager at a warehouse for a non-profit organisation, some time with filling out the time sheets/logs for staff and volunteers.
She's not very good at converting AM/PM to 24H format which would basically fix our little "problem".
Excel is fine when you're in AM territory. (ie. 7:00 means 7:00 AM). Times in the afternoon are a bit more tricky, as, as far as I know, it isn't possible to format cells purely for PM. Requiring to type 3:00 p (or PM), which Excel then will interpret as 3:00 PM.
So, in my quest to speed up things I figured it should be possible for her to enter a "simple" number (Table row 3) and then have excel convert it to the proper time (table row 4).
<tbody>
</tbody>
I found a macro on the WWW which does basically exactly what I want it to do, except it still doesn't make a distinction between AM and PM (when time isn't entered in 24H format). My macro skills are limited, usually I'm able to figure stuff out though and alter/edit so they work for me, but now it has me a bit stumped.
The weird thing is that the macro as is, seems to be working for the whole sheet instead of the range. Even when I change it to say ("C6:C28") it still changes times in other cells as well. I don't get it...
My end goal is to add multiple ranges, so time entered into columns:
C, E, G, I, K will be interpreted as AM
D, F, H, J, L will be interpreted as PM
Thanks in advance for any help.
Here is the macro:
I'm trying to save my girlfriend, who is a manager at a warehouse for a non-profit organisation, some time with filling out the time sheets/logs for staff and volunteers.
She's not very good at converting AM/PM to 24H format which would basically fix our little "problem".
Excel is fine when you're in AM territory. (ie. 7:00 means 7:00 AM). Times in the afternoon are a bit more tricky, as, as far as I know, it isn't possible to format cells purely for PM. Requiring to type 3:00 p (or PM), which Excel then will interpret as 3:00 PM.
So, in my quest to speed up things I figured it should be possible for her to enter a "simple" number (Table row 3) and then have excel convert it to the proper time (table row 4).
A | B | C | D | E | F | G | H | I | J | K | L | M | |
1 | Monday | Monday | Tuesday | Tuesday | Wednesday | Wednesday | Thursday | Thursday | Friday | Friday | Week Total | ||
2 | In | Out | In | Out | In | Out | In | Out | In | Out | |||
3 | 715 | 300 | 700 | 300 | 700 | 300 | 700 | 300 | 700 | 230 | 38:15 | ||
4 | 7:15 AM | 3:00 PM | 7:00 AM | 3:00 PM | 7:00 AM | 3:00 PM | 7:00 AM | 3:00 PM | 7:00 PM | 2:30 PM | 38:15 | ||
5 |
<tbody>
</tbody>
I found a macro on the WWW which does basically exactly what I want it to do, except it still doesn't make a distinction between AM and PM (when time isn't entered in 24H format). My macro skills are limited, usually I'm able to figure stuff out though and alter/edit so they work for me, but now it has me a bit stumped.
The weird thing is that the macro as is, seems to be working for the whole sheet instead of the range. Even when I change it to say ("C6:C28") it still changes times in other cells as well. I don't get it...
My end goal is to add multiple ranges, so time entered into columns:
C, E, G, I, K will be interpreted as AM
D, F, H, J, L will be interpreted as PM
Thanks in advance for any help.
Here is the macro:
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("C6:L28")) Is Nothing Then
End If
If Target.Cells.Count > 1 Then
Exit Sub
End If
If Target.Value = "" Then
Exit Sub
End If
Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 3
TimeStr = Left(.Value, 1) & ":" & _
Right(.Value, 2)
Case 4
TimeStr = Left(.Value, 2) & ":" & _
Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub