Data Validation and Current Time

jkjohnson0723

New Member
Joined
Jun 2, 2015
Messages
3
I am creating a spreadsheet for users to punch in an out after they have completed a task. I need to ensure that these entries are honest. Is there a formula I can use that will ensure a user can only enter the current time in a given cell?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
when you say punch, how will that operate, whats your concept and limitations
 

jkjohnson0723

New Member
Joined
Jun 2, 2015
Messages
3
The users will need to insert the current time into a given field. They know they should be using the shortcut (ctrl+shift+;). I want to make sure that the user can not manually enter a non-current time.
 

mole999

Well-known Member
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
you could put this in
Code:
Sub stamper()
On Error Resume Next
Dim LR As Long
Dim lastrow
LR = Sheets("Track").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sheets("Track").Range("A" & LR) = Now()
Sheets("Track").Range("B" & LR) = Environ("USERNAME ")
End Sub
 

jkjohnson0723

New Member
Joined
Jun 2, 2015
Messages
3
I would like to enter a simple formula in the Time tab in the data validation screen. I can do this with a date by entering =TODAY() but it will not let me do it with =NOW()-TRUNC(NOW()).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,559
Messages
5,832,467
Members
430,136
Latest member
Asir Jefferson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top