Problem with default date/Time

[mward04]

I'm working with some hospitals tracking the times patients come in and
out of the ER. Times are entered in Excel and then imported into
Access. Since patients can obviously leave the ER after Midnight, I
enter the times as 24 hours PLUS the time they left i.e. 3AM=27:00
which translates to 1/1/1900 3:00AM. This avoids having to write in
the date which would double the amount of time I spend on this project.
However, since the default date in Excel is 1/0/1900 when I import this
into Access, Access interprets the date as 12/30/1899.

Is there a way to change the default date of 1/0/1900 to say, 1/1/1900
or any other time?

Any other possible solutions?

Thanks!
Mike

 

[Bernie Deitrick]

Mike,

The best solution is for you to enter the date and time. However, if you are looking for a
shortcut, then try this code below.

--
HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Col1 As Integer
Dim Col2 As Integer

Dim Col1 As Integer
If Target.Column <> 2 And Target.Column <> 4 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value + Range("A2").Value
Application.EnableEvents = True
End Sub

 

[Guest]

Consider adding 48 hours instead of 24 hours.

 

[Bernie Deitrick]

Mike,

Sorry about that - fat fingers....

Try the code below. Enter the base date in cell A2 - say you are working on the sheets from March
9, so enter that date into that cell.

The code will enter a date and time into cells in columns 2 (B) and 4 (D), using the base date
entered in cell A2.

You can change the base cell and the columns that are modified by changing the code.

Copy the code, right-click the sheet tab, select "View Code" and paste the code in the window that
appears.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Col1 As Integer
Dim Col2 As Integer

Col1 = 2
Col2 = 4

If Target.Column <> Col1 And Target.Column <> Col2 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value + Range("A2").Value
Application.EnableEvents = True
End Sub