software

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: www.excelforum.com/member.php...oamp;userid=32407
View this thread: www.excelforum.com/showthread...hreadid=521733Mike,

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 lt;gt; 2 And Target.Column lt;gt; 4 Then Exit Sub
If Target.Cells.Count gt; 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value Range(quot;A2quot;).Value
Application.EnableEvents = True
End Sub
quot;mward04quot; gt; wrote in message
...
gt;
gt; I'm working with some hospitals tracking the times patients come in and
gt; out of the ER. Times are entered in Excel and then imported into
gt; Access. Since patients can obviously leave the ER after Midnight, I
gt; enter the times as 24 hours PLUS the time they left i.e. 3AM=27:00
gt; which translates to 1/1/1900 3:00AM. This avoids having to write in
gt; the date which would double the amount of time I spend on this project.
gt; However, since the default date in Excel is 1/0/1900 when I import this
gt; into Access, Access interprets the date as 12/30/1899.
gt;
gt; Is there a way to change the default date of 1/0/1900 to say, 1/1/1900
gt; or any other time?
gt;
gt; Any other possible solutions?
gt;
gt; Thanks!
gt; Mike
gt;
gt;
gt; --
gt; mward04
gt; ------------------------------------------------------------------------
gt; mward04's Profile: www.excelforum.com/member.php...oamp;userid=32407
gt; View this thread: www.excelforum.com/showthread...hreadid=521733
gt;
Consider adding 48 hours instead of 24 hours.
--
Gary''s Studentquot;mward04quot; wrote:

gt;
gt; I'm working with some hospitals tracking the times patients come in and
gt; out of the ER. Times are entered in Excel and then imported into
gt; Access. Since patients can obviously leave the ER after Midnight, I
gt; enter the times as 24 hours PLUS the time they left i.e. 3AM=27:00
gt; which translates to 1/1/1900 3:00AM. This avoids having to write in
gt; the date which would double the amount of time I spend on this project.
gt; However, since the default date in Excel is 1/0/1900 when I import this
gt; into Access, Access interprets the date as 12/30/1899.
gt;
gt; Is there a way to change the default date of 1/0/1900 to say, 1/1/1900
gt; or any other time?
gt;
gt; Any other possible solutions?
gt;
gt; Thanks!
gt; Mike
gt;
gt;
gt; --
gt; mward04
gt; ------------------------------------------------------------------------
gt; mward04's Profile: www.excelforum.com/member.php...oamp;userid=32407
gt; View this thread: www.excelforum.com/showthread...hreadid=521733
gt;
gt;

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 quot;View Codequot; 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 lt;gt; Col1 And Target.Column lt;gt; Col2 Then Exit Sub
If Target.Cells.Count gt; 1 Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value Range(quot;A2quot;).Value
Application.EnableEvents = True
End Subquot;mward04quot; gt; wrote in message
...
gt;
gt; I'm working with some hospitals tracking the times patients come in and
gt; out of the ER. Times are entered in Excel and then imported into
gt; Access. Since patients can obviously leave the ER after Midnight, I
gt; enter the times as 24 hours PLUS the time they left i.e. 3AM=27:00
gt; which translates to 1/1/1900 3:00AM. This avoids having to write in
gt; the date which would double the amount of time I spend on this project.
gt; However, since the default date in Excel is 1/0/1900 when I import this
gt; into Access, Access interprets the date as 12/30/1899.
gt;
gt; Is there a way to change the default date of 1/0/1900 to say, 1/1/1900
gt; or any other time?
gt;
gt; Any other possible solutions?
gt;
gt; Thanks!
gt; Mike
gt;
gt;
gt; --
gt; mward04
gt; ------------------------------------------------------------------------
gt; mward04's Profile: www.excelforum.com/member.php...oamp;userid=32407
gt; View this thread: www.excelforum.com/showthread...hreadid=521733
gt;