V
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
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