Time Calculations

[vepha]

Hi Everyone,

I am importing records via excel from another program. The problem is some
fields has different values then usual. See below please.

Importdate Name Checkin is Checkout is
13.04.2008 Alice 21-23 04:52
13.04.2008 Alex 20:48 04*52
13.04.2008 Max 01:17 10:24
13.04.2008 Micheal 13:35 22:45
13.04.2008 George 18:50 02*30
13.04.2008 Alexandra 22-50 05:00
13.04.2008 Jen 18-55 02:25
14.04.2008 Alice 21-35 05:10
14.04.2008 Alex 20:30 04*55
14.04.2008 Max 01:01 10:05
14.04.2008 Micheal
14.04.2008 George 18:45 02*05
14.04.2008 Alexandra 22-30 04:55
14.04.2008 Jen 18-51 02:22

The Importdates arent always the Checkin or Checkout dates. If the Checkin
time has a " - " sign instead of " : ", that means it is from previous day of
import date. If the Checkout time has a " * " sign instead of " : " , that
means it is from the next day of importdate.

First Question is how to convert those times as below in "Checkin should be"
and "Checkout should be":

Importdate Name Checkin is Checkout is Checkin should be Checkout should be
13.04.2008 Alice 21-23 04:52 12.04.2008 21:23 13.04.2008 04:52
13.04.2008 Alex 20:48 04*52 13.04.2008 20:48 14.04.2008 04:52
13.04.2008 Max 01:17 10:24 13.04.2008 01:17 13.04.2008 10:24
13.04.2008 Micheal 13:35 22:45 13.04.2008 13:35 13.04.2008 22:45
13.04.2008 George 18:50 02*30 13.04.2008 18:50 14.04.2008 02:30
13.04.2008 Alexandra 22-50 05:00 12.04.2008 22:50 13.04.2008 05:00
13.04.2008 Jen 18-55 02:25 12.04.2008 18:55 13.04.2008 02:25
14.04.2008 Alice 21-35 05:10 13.04.2008 21:35 14.04.2008 05:10
14.04.2008 Alex 20:30 04*55 14.04.2008 20:30 15.04.2008 04:55
14.04.2008 Max 01:01 10:05 14.04.2008 01:01 14.04.2008 10:05
14.04.2008 Micheal
14.04.2008 George 18:45 02*05 14.04.2008 18:45 15.04.2008 02:05
14.04.2008 Alexandra 22-30 04:55 13.04.2008 22:30 14.04.2008 04:55
14.04.2008 Jen 18-51 02:22 13.04.2008 18:51 14.04.2008 02:22
15.04.2008 Micheal 13:30 22:40 15.04.2008 13:30 15.04.2008 22:40

The second question is how to calculate the difference in Hours between
Checkout should be (now) and Checkin should be (next record from same person
where Checkin exists).

I have tried to solve this Problem in excel before importing records but was
complicated for someoneelse to do the same work ( which may give wrong values
) everytime before import.

Any help would be greatly appreciated.
Regards,
vepha

 

[vepha]

Thanks a lot , i will test it as sooon as possible.

Again, thanks for the reply and help.

regards,
vepha

 

[vepha]

got help somewhereelse. Thanks to rpeare



http://forums.aspfree.com/microsoft-access-help-18/time-calculation-225433.html