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?
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mole999

Moderator
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

Moderator
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()).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,961
Messages
5,599,061
Members
414,281
Latest member
Engjamal2021

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