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



"mward04" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> mward04
> ------------------------------------------------------------------------
> mward04's Profile: http://www.excelforum.com/member.php...o&userid=32407
> View this thread: http://www.excelforum.com/showthread...hreadid=521733
>

[Gary''s Student]

Consider adding 48 hours instead of 24 hours.
--
Gary''s Student


"mward04" wrote:

>
> 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
>
>
> --
> mward04
> ------------------------------------------------------------------------
> mward04's Profile: http://www.excelforum.com/member.php...o&userid=32407
> View this thread: http://www.excelforum.com/showthread...hreadid=521733
>
>

[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


"mward04" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> mward04
> ------------------------------------------------------------------------
> mward04's Profile: http://www.excelforum.com/member.php...o&userid=32407
> View this thread: http://www.excelforum.com/showthread...hreadid=521733
>

[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


"mward04" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> mward04
> ------------------------------------------------------------------------
> mward04's Profile: http://www.excelforum.com/member.php...o&userid=32407
> View this thread: http://www.excelforum.com/showthread...hreadid=521733
>

[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


"mward04" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> mward04
> ------------------------------------------------------------------------
> mward04's Profile: http://www.excelforum.com/member.php...o&userid=32407
> View this thread: http://www.excelforum.com/showthread...hreadid=521733
>