Use todays date when user enters time in Excel

[PureBluff]

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

 

[Yongle]

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

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]

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!