Excel Timesheet

[trigirl]

I have an Excel timesheet, and I am wondering if there is a way to have the timesheet default to PM after 12 noon, so the employees dont have to put in PM, they would just put their time and the sheet would default it to PM.

 

[daniels012]

Need more details!
How do you enter time? Macro or button?
The more details you can give would help!

Michael

 

[trigirl]

The employees enter their time manually. The problem we having is that they forget to put AM/PM and it messes up our other reports, so I wanted to make it as fool proof as possible.

 

[CyberGuy2004]

If you don't have anyone crossing midnight or working longer than 12 hours at a stretch, your "hours worked" formula can check to see if the stop time is before the start time, adding 12 hours. If start time is in Col B, stop time is in Col C and hours worked is Col D, the D2 would have:

=24*(C2-B2+IF(C2<B2,0.5,0))

or

24*MOD(C2-B2,0.5)

I suppose you could put some code into the Worksheet_Change event, and have it check the NOW function, adding 0.5 if the current time is later than 1:00 PM. I have very little experience with event-handling, so this is affirmation of concept. Any event-wizards here today?

 

[trigirl]

How could we enter with a button?

 

[daniels012]

there are a number of way to get this done.

1 Go to military time
2 Create a combo box with only the times you have in the combobox

can you give a sample of the times: Do you go by minutes, 5 minutes, etc.

We go by Quarter hours, so I created a combobox that only has times entered in quarter hours.

Michael

 

[daniels012]

Create a button using the Forms Menu.

Type in "EnterTime"
Then create. Just copy and paste this code

Sub EnterTime()
    InpBx1 = InputBox("Enter the hour you want.")
    InpBx2 = InputBox("enter the minutes you want.")
    InPBx3 = InputBox("Enter AM or PM ")
    Application.ActiveCell.Value = InpBx1 & ":" & InpBx2 & ":" & "00 " & InPBx3
End Sub

The user will be asked the hour.... the minutes..... then AM or PM


Michael

 

[trigirl]

Ok this button works great, but I do have question. I want to lock the cells so that the only way they can enter time is by clicking the button, but when I lock the cells it sends a message saying it cant update the cells because it is locked. Is there a way to make this the button the only way to enter time?

 

[daniels012]

Ok
Try this:
Sub EnterTime()
ActiveSheet.Unprotect
InpBx1 = InputBox("Enter the hour you want.")
InpBx2 = InputBox("enter the minutes you want.")
InPBx3 = InputBox("Enter AM or PM ")
Application.ActiveCell.Value = InpBx1 & ":" & InpBx2 & ":" & "00 " & InPBx3
ActiveSheet.Protect
End Sub
Michael

 

[trigirl]

Ok, we are almost there. This has it prompt to put in the password to unprotect the sheet, but the employees don't know the password. Is there a way to have the script put in the password?