Use todays date when user enters time in Excel

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
Hi,

I'm not sure if this can be done, anyone have any ideas?

I want a user to be able to only enter a time in a specific cell
I wish Excel to display this as hh:mm
I would like Excel to store this value as todays date, so if I change to "dd-mm-yy hh:mm", or measure against this value, it's not set to Jan 1900 on the date element, but the current date for time being entered.

Hope that makes sense.

Any help greatly appreciated.
Thanks
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Try this combination of data validation and VBA

1.Data validation - DO THIS FIRST
Select the specified cell \ click Data Tab \ Data Validation ...
Use these settings:
Allow : Time
Between: Start Time 00:00:00 End Time 23:59:00

2. VBA
Place code below in SHEET module and amend A1 to the specific cell required
Right-click on sheet tab \ View Code \ paste code into code window \ {ALT}{F11} to return to Excel
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Const Ref As String = "A1"
    On Error GoTo ReEnable
   
    With Target
        If .Address(0, 0) = Ref Then
            Application.EnableEvents = False
            .Value = Date + Evaluate("TIME(HOUR(" & Ref & "),MINUTE(" & Ref & "),0)")
            .NumberFormat = "hh:mm"
        End If
    End With
ReEnable:
    Application.EnableEvents = True
End Subb
 

PureBluff

Board Regular
Joined
Apr 4, 2014
Messages
155
Thanks for the very fast response Yongle, I'm just tying off another project & I'll try this & let you know how I get on!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,313
Messages
5,635,499
Members
416,860
Latest member
coen078

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