Suljettu

Simple Time Clock Reporter web app or Excel VBA

Time Clock Project

Our company uses a biometric time clock that employees use to"clock-in" and "clock-out" each day. They do not clock-in and out of the system for breaks or lunch. Theoretically, each employee will be recorded on the system's log twice each day, once around 10am and once at around 6 pm. Employees sometimes forget to clock-in or clock-out (see BUSINESS EXCEPTIONS below for guidance).

Our time clock creates a .TXT log file (see DATA SAMPLE below) which adds a record each time someone clocks-in or out of the system. We'd like to automated the reporting of the process through your work.

Our idea is that the log file (.TXT type file) is either copied into a spreadsheet (in which case this becomes an Excel with VBA project), or even better, the .TXT file is uploaded to a website where a small web app does the reporting (in which case we don't really care what technology is used to build it, as long as it works in a Firefox browser and runs on our Mediatemple server).

We've posted an example of what the reports might look like here

[url removed, login to view]

Note, the example is just an idea. In order to keep your time to a minimum, we're very flexible in accepting the format and design of the report and the way data is handled, just so long as all of the information is presented and appropriately calculated and flagged.

PROJECT OVERVIEW

* We will need a simple way of importing the data into the Excel file from the .TXT file or uploading the file to your web app depending on which strategy is used.

* The resulting program should automatically process the necessary reporting information according to the BUSINESS EXCEPTIONS and REPORT REQUIREMENTS (see below).

* The records supplied to the spreadsheet will always be variable in number, but consistent in format.

* A sample of the exact data format is supplied below (see DATA SAMPLE).

BUSINESS EXCEPTIONS

* The log only knows that a specific employee interfaced with the timeclock as a specific time, it does not know if the employee was clocking in or clocking out. Employees sometimes forget to clock in or clock out. We have developed specific rules to address this situation:

o If an employee appears to still be clocked in at 3:00 a.m. they should be considered to have forgotten to clock out the previous day, and it should be considered that they clocked out at 5:59 PM the previous day

o If the first login of the day happens after 3:00 PM, it should be considered that the employee forgot to clock in earlier that morning, and they should be considered to have clocked in at 10:20AM earlier that day.

* Saturday and Sunday have no rules. Any clock-ins or clock-outs at any time on Saturdays and Sundays are calculated based on actual times. If only one timestamp is available on a Saturday or Sunday (for example, they came in on a weekend, but either forgot to clock-in or clock out) the time should be reported, but no time will be calculated for that day.

REPORT REQUIREMENTS (see link to sample above)

Based on the above rules and the time data that we import into the program, we need the following data for reporting:

* All reports should have dropdowns menus to facilitate narrowing the reporting time. For example, we can upload 3 months worth of data, but only run reports from date A to date B within that span of time (say, a specific week or month)

* All reports should begin with a header stating the period of time that the uploaded data covers.

* Report by employee shows the clock in and clock out for each day for that employee and visually flags clock ins or clock outs that where the above BUSINESS EXCEPTIONS were invoked (orange background on example report link).

* The report should calculate the total hours and minutes worked by each employee for the designated period

* The report should indicate how many times exceptions were invokes during the period and the percentage of exceptions

* The report should visually indicate clock in times that are later than 10am and earlier than 6pm (yellow background on example report link)

* The report should calculate the number of late clock-ins and early clock-outs for the period and the percentage of late clock-ins and early clock-outs for the period,and visually indicate these (red text in example report link).

DATA SAMPLE

The sample below is the exact format of the data we pull out of the time clock.

No = Record number and is auto incremented

TMNo = Will always be "1" and is not important for our purposes

EnNo = Correlates to the employee number and is the basis for the reports by employee

Name = Is always blank

GMNo = Will always be "1" and is not important for our purposes

Mode = Will always be "1" and is not important for our purposes

DateTime = the timestamp when the employee (EnNo) clocked in or out

No TMNo EnNo Name GMNo Mode DateTime

1 1 1 1 1 05/13/2008 17:11:41

2 1 1 1 1 05/13/2008 17:11:54

3 1 1 1 1 05/13/2008 17:12:06

4 1 1 1 1 05/13/2008 17:12:27

5 1 1 1 1 05/13/2008 17:20:17

6 1 3 1 1 05/13/2008 18:24:23

7 1 1 1 1 05/13/2008 19:47:00

8 1 1 1 1 05/14/2008 09:58:50

9 1 3 1 1 05/14/2008 11:07:48

10 1 3 1 1 05/14/2008 11:07:49

11 1 1 1 1 05/14/2008 19:09:27

12 1 3 1 1 05/14/2008 19:37:12

13 1 1 1 1 05/15/2008 09:39:09

14 1 3 1 1 05/15/2008 09:53:47

15 1 3 1 1 05/15/2008 13:25:30

16 1 1 1 1 05/15/2008 15:54:15

17 1 1 1 1 05/15/2008 17:46:43

18 1 1 1 1 05/16/2008 10:01:13

19 1 1 1 1 05/16/2008 19:25:35

20 1 1 1 1 05/17/2008 15:51:21

21 1 1 1 1 05/20/2008 09:38:43

22 1 3 1 1 05/20/2008 10:00:15

23 1 3 1 1 05/20/2008 18:38:43

24 1 1 1 1 05/20/2008 19:52:23

25 1 3 1 1 05/21/2008 10:05:38

26 1 1 1 1 05/21/2008 10:49:29

27 1 1 1 1 05/21/2008 16:47:59

28 1 3 1 1 05/21/2008 18:16:11

29 1 3 1 1 05/22/2008 10:37:40

30 1 1 1 1 05/22/2008 11:08:21

31 1 3 1 1 05/22/2008 18:39:52

32 1 3 1 1 05/23/2008 10:07:22

33 1 3 1 1 05/23/2008 17:26:15

34 1 1 1 1 05/25/2008 18:24:56

35 1 1 1 1 05/27/2008 09:01:04

36 1 3 1 1 05/27/2008 10:02:47

37 1 3 1 1 05/27/2008 18:04:55

38 1 1 1 1 05/27/2008 18:37:13

39 1 1 1 1 05/27/2008 19:40:57

40 1 1 1 1 05/27/2008 19:53:43

41 1 1 1 1 05/28/2008 09:54:39

42 1 3 1 1 05/28/2008 09:59:58

43 1 3 1 1 05/28/2008 18:25:29

44 1 1 1 1 05/28/2008 19:11:01

45 1 1 1 1 05/29/2008 10:03:52

46 1 3 1 1 05/29/2008 10:07:33

47 1 3 1 1 05/29/2008 18:12:47

48 1 1 1 1 05/29/2008 18:46:17

49 1 1 1 1 05/30/2008 09:54:48

50 1 1 1 1 06/02/2008 09:51:40

51 1 3 1 1 06/02/2008 10:02:14

52 1 1 1 1 06/02/2008 12:21:04

53 1 1 1 1 06/02/2008 12:21:12

54 1 3 1 1 06/02/2008 17:50:16

55 1 1 1 1 06/02/2008 19:10:09

56 1 3 1 1 06/03/2008 09:57:15

57 1 1 1 1 06/03/2008 10:15:30

58 1 1 1 1 06/03/2008 10:15:36

59 1 3 1 1 06/03/2008 17:55:18

60 1 1 1 1 06/04/2008 09:57:10

61 1 3 1 1 06/04/2008 10:03:19

62 1 3 1 1 06/04/2008 18:09:42

63 1 1 1 1 06/05/2008 20:41:11

64 1 3 1 1 06/06/2008 17:32:53

65 1 1 1 1 06/09/2008 09:20:44

66 1 3 1 1 06/09/2008 10:12:18

67 1 3 1 1 06/09/2008 18:19:00

68 1 1 1 1 06/09/2008 18:51:47

69 1 3 1 1 06/10/2008 09:59:50

70 1 1 1 1 06/10/2008 10:14:05

71 1 3 1 1 06/10/2008 18:12:04

72 1 3 1 1 06/11/2008 10:01:48

73 1 3 1 1 06/11/2008 17:20:34

74 1 1 1 1 06/12/2008 09:54:11

75 1 3 1 1 06/12/2008 10:10:11

76 1 3 1 1 06/12/2008 18:15:44

77 1 3 1 1 06/13/2008 09:54:29

78 1 1 1 1 06/13/2008 10:38:04

79 1 3 1 1 06/13/2008 18:01:21

80 1 1 1 1 06/16/2008 09:47:53

81 1 3 1 1 06/16/2008 10:00:22

82 1 3 1 1 06/16/2008 18:23:22

83 1 1 1 1 06/16/2008 20:37:43

84 1 1 1 1 06/17/2008 09:51:05

85 1 3 1 1 06/17/2008 10:02:30

86 1 3 1 1 06/17/2008 15:07:15

87 1 3 1 1 06/23/2008 10:05:18

88 1 1 1 1 06/23/2008 14:19:50

89 1 3 1 1 06/23/2008 18:39:10

90 1 1 1 1 06/30/2008 10:00:39

91 1 3 1 1 06/30/2008 10:07:46

92 1 3 1 1 06/30/2008 21:10:01

93 1 3 1 1 07/01/2008 10:02:07

94 1 3 1 1 07/01/2008 17:44:54

95 1 3 1 1 07/02/2008 17:55:31

96 1 3 1 1 07/03/2008 10:06:01

97 1 3 1 1 07/07/2008 09:56:52

98 1 3 1 1 07/08/2008 10:03:02

99 1 3 1 1 07/08/2008 17:50:51

100 1 1 1 1 07/09/2008 09:53:17

101 1 3 1 1 07/09/2008 10:06:27

102 1 3 1 1 07/09/2008 18:02:10

103 1 1 1 1 07/10/2008 09:26:25

104 1 3 1 1 07/10/2008 10:05:43

105 1 1 1 1 07/10/2008 18:42:33

106 1 3 1 1 07/11/2008 10:13:01

107 1 1 1 1 07/14/2008 10:02:02

108 1 3 1 1 07/14/2008 11:22:27

109 1 1 1 1 07/22/2008 08:41:45

110 1 1 1 1 07/25/2008 09:51:02

111 1 1 1 1 07/28/2008 09:17:22

112 1 3 1 1 07/29/2008 09:59:53

113 1 3 1 1 07/29/2008 18:36:01

114 1 3 1 1 07/31/2008 09:53:32

115 1 3 1 1 07/31/2008 17:21:54

116 1 3 1 1 08/01/2008 10:09:28

117 1 1 1 1 08/04/2008 08:51:39

118 1 1 1 1 08/04/2008 19:48:07

119 1 1 1 1 08/05/2008 10:03:26

120 1 3 1 1 08/06/2008 10:00:17

121 1 3 1 1 08/07/2008 16:24:07

122 1 1 1 1 08/07/2008 16:25:28

123 1 2 1 1 08/07/2008 16:26:10

124 1 3 1 1 08/07/2008 16:27:47

125 1 5 1 1 08/07/2008 16:43:17

126 1 2 1 1 08/07/2008 18:06:37

127 1 1 1 1 08/08/2008 08:31:06

128 1 4 1 1 08/08/2008 09:42:02

129 1 3 1 1 08/08/2008 09:56:47

130 1 5 1 1 08/08/2008 09:59:45

131 1 2 1 1 08/08/2008 10:08:54

132 1 5 1 1 08/08/2008 17:55:58

133 1 3 1 1 08/08/2008 18:00:54

134 1 2 1 1 08/08/2008 18:12:29

135 1 4 1 1 08/08/2008 18:13:36

136 1 1 1 1 08/08/2008 18:55:16

137 1 3 1 1 08/11/2008 09:08:42

138 1 4 1 1 08/11/2008 09:08:47

139 1 2 1 1 08/11/2008 09:59:35

140 1 5 1 1 08/11/2008 10:05:29

141 1 7 1 1 08/11/2008 12:33:12

142 1 8 1 1 08/11/2008 12:34:54

143 1 2 1 1 08/11/2008 13:14:35

144 1 5 1 1 08/11/2008 18:21:58

145 1 3 1 1 08/11/2008 18:29:24

146 1 4 1 1 08/11/2008 18:32:03

147 1 7 1 1 08/11/2008 18:43:11

148 1 1 1 1 08/12/2008 08:51:50

149 1 8 1 1 08/12/2008 09:34:42

150 1 7 1 1 08/12/2008 09:40:33

151 1 2 1 1 08/12/2008 09:48:51

152 1 5 1 1 08/12/2008 10:00:31

153 1 3 1 1 08/12/2008 10:04:42

154 1 6 1 1 08/12/2008 13:57:37

155 1 6 1 1 08/12/2008 14:33:26

156 1 7 1 1 08/12/2008 16:13:25

157 1 6 1 1 08/12/2008 17:36:48

158 1 5 1 1 08/12/2008 17:47:39

159 1 2 1 1 08/12/2008 18:11:29

160 1 4 1 1 08/12/2008 18:36:05

161 1 4 1 1 08/13/2008 09:48:23

162 1 2 1 1 08/13/2008 10:03:04

163 1 6 1 1 08/13/2008 10:28:47

164 1 8 1 1 08/13/2008 10:40:34

165 1 1 1 1 08/13/2008 14:17:46

166 1 3 1 1 08/13/2008 14:38:41

167 1 4 1 1 08/13/2008 16:23:59

168 1 3 1 1 08/13/2008 17:45:13

169 1 5 1 1 08/13/2008 17:50:06

170 1 6 1 1 08/13/2008 18:09:04

171 1 2 1 1 08/13/2008 18:12:39

172 1 1 1 1 08/14/2008 09:42:09

173 1 4 1 1 08/14/2008 09:51:30

174 1 2 1 1 08/14/2008 09:54:12

175 1 7 1 1 08/14/2008 09:54:16

176 1 7 1 1 08/14/2008 09:54:17

177 1 3 1 1 08/14/2008 10:07:32

178 1 5 1 1 08/14/2008 10:09:15

179 1 2 1 1 08/14/2008 17:35:32

180 1 3 1 1 08/14/2008 17:39:15

181 1 7 1 1 08/14/2008 18:37:06

182 1 7 1 1 08/14/2008 18:37:07

183 1 4 1 1 08/14/2008 18:37:11

184 1 7 1 1 08/15/2008 09:59:09

185 1 7 1 1 08/15/2008 09:59:10

186 1 1 1 1 08/15/2008 09:59:59

187 1 2 1 1 08/15/2008 10:04:33

188 1 2 1 1 08/15/2008 10:04:38

189 1 6 1 1 08/15/2008 10:04:46

190 1 3 1 1 08/15/2008 10:14:21

191 1 8 1 1 08/15/2008 10:16:05

192 1 9 1 1 08/15/2008 15:30:15

193 1 5 1 1 08/15/2008 18:12:28

194 1 7 1 1 08/15/2008 18:54:31

195 1 3 1 1 08/15/2008 19:02:42

196 1 1 1 1 08/15/2008 20:30:25

197 1 9 1 1 08/18/2008 09:41:00

198 1 9 1 1 08/18/2008 09:41:01

199 1 4 1 1 08/18/2008 09:53:56

200 1 1 1 1 08/18/2008 09:59:19

201 1 6 1 1 08/18/2008 09:59:30

202 1 3 1 1 08/18/2008 10:01:40

203 1 2 1 1 08/18/2008 10:04:24

204 1 7 1 1 08/18/2008 10:11:03

205 1 7 1 1 08/18/2008 10:11:04

206 1 5 1 1 08/18/2008 10:12:39

207 1 4 1 1 08/18/2008 18:12:54

208 1 9 1 1 08/18/2008 18:13:14

209 1 8 1 1 08/18/2008 18:29:09

210 1 5 1 1 08/18/2008 18:30:13

211 1 3 1 1 08/18/2008 18:51:12

212 1 2 1 1 08/18/2008 18:54:10

213 1 7 1 1 08/18/2008 18:55:26

214 1 7 1 1 08/18/2008 18:55:27

215 1 4 1 1 08/19/2008 10:03:17

216 1 7 1 1 08/19/2008 10:04:18

217 1 2 1 1 08/19/2008 10:10:04

218 1 5 1 1 08/19/2008 10:11:33

219 1 8 1 1 08/19/2008 10:13:15

220 1 5 1 1 08/19/2008 18:06:42

221 1 4 1 1 08/19/2008 18:16:29

222 1 8 1 1 08/19/2008 18:34:03

223 1 7 1 1 08/19/2008 18:34:06

224 1 7 1 1 08/19/2008 18:34:07

225 1 4 1 1 08/20/2008 09:34:08

226 1 9 1 1 08/20/2008 09:34:35

227 1 9 1 1 08/20/2008 09:34:36

228 1 8 1 1 08/20/2008 10:09:45

229 1 7 1 1 08/20/2008 10:22:39

230 1 7 1 1 08/20/2008 10:22:40

231 1 2 1 1 08/20/2008 10:30:11

232 1 5 1 1 08/20/2008 11:00:27

233 1 5 1 1 08/20/2008 18:31:33

234 1 7 1 1 08/20/2008 18:37:23

235 1 7 1 1 08/20/2008 18:37:24

236 1 2 1 1 08/20/2008 18:37:25

237 1 4 1 1 08/20/2008 18:37:38

238 1 8 1 1 08/20/2008 18:37:50

239 1 7 1 1 08/21/2008 09:25:34

240 1 7 1 1 08/21/2008 09:25:35

241 1 9 1 1 08/21/2008 09:34:42

242 1 9 1 1 08/21/2008 09:34:43

243 1 4 1 1 08/21/2008 10:15:17

244 1 5 1 1 08/21/2008 10:19:34

245 1 8 1 1 08/21/2008 10:21:04

246 1 4 1 1 08/21/2008 17:23:41

247 1 9 1 1 08/21/2008 18:00:09

248 1 9 1 1 08/21/2008 18:00:10

249 1 2 1 1 08/21/2008 18:12:23

250 1 5 1 1 08/21/2008 18:33:44

251 1 7 1 1 08/21/2008 18:33:58

252 1 7 1 1 08/21/2008 18:33:58

253 1 9 1 1 08/22/2008 09:37:10

254 1 9 1 1 08/22/2008 09:37:11

255 1 5 1 1 08/22/2008 10:20:44

256 1 5 1 1 08/22/2008 10:20:45

257 1 7 1 1 08/22/2008 10:20:48

258 1 7 1 1 08/22/2008 10:20:49

259 1 4 1 1 08/22/2008 10:44:56

260 1 5 1 1 08/22/2008 12:11:52

261 1 4 1 1 08/22/2008 17:25:48

262 1 9 1 1 08/22/2008 17:38:24

263 1 9 1 1 08/22/2008 17:38:28

264 1 9 1 1 08/22/2008 17:38:32

265 1 9 1 1 08/22/2008 17:38:35

266 1 7 1 1 08/22/2008 17:49:49

267 1 7 1 1 08/22/2008 17:49:50

268 1 4 1 1 08/25/2008 09:49:15

269 1 7 1 1 08/25/2008 10:01:27

270 1 7 1 1 08/25/2008 10:01:28

271 1 6 1 1 08/25/2008 10:03:49

272 1 2 1 1 08/25/2008 10:13:41

273 1 5 1 1 08/25/2008 10:23:49

274 1 4 1 1 08/25/2008 17:35:56

275 1 5 1 1 08/25/2008 17:54:28

276 1 6 1 1 08/25/2008 17:54:40

277 1 2 1 1 08/25/2008 18:47:31

278 1 7 1 1 08/25/2008 18:47:45

279 1 7 1 1 08/25/2008 18:47:46

280 1 8 1 1 08/26/2008 10:04:00

281 1 6 1 1 08/26/2008 10:04:08

282 1 2 1 1 08/26/2008 10:04:27

283 1 7 1 1 08/26/2008 10:10:09

284 1 7 1 1 08/26/2008 10:10:10

285 1 5 1 1 08/26/2008 10:17:10

Taidot: Excel, PHP, Python, Ruby on Rails, verkkosivujen suunnittelu

Näytä lisää: tmno enno name gmno mode datetime, time clock app excel, tmno enno name gmno mode, excel vba clock, excel time clock, use web clock excel, clock vba, use excel time clock, enno gmno, tmno enno gmno mode, biometric text file tmno, simple clock, simple clock system vba excel, tmno enno gmno, vba time clock, excel clock, vba excel clock, time clock excel, clock vba excel, web app clock, excel web app vba, what web technology does google use, weekend work, website 101, web format

About the Employer:
( 1 review ) Union, United States

Projektin tunnus: #413029

16 freelanceria on tarjonnut keskimäärin 127 $ tähän työhön

agihcam

It can be done in Excel, refer to PMB.

200 $ USD 3 päivässä
(39 arvostelua)
5.1
majid4466

As detailed in my post on the Project Clarification Board.

60 $ USD 2 päivässä
(30 arvostelua)
5.1
dev3dworx

Excel would be a good choice. If you would like to have it in VBA (Add-in) with customized user interface like toolbar controls and menubar options then consider my proposal.

149.99 $ USD 4 päivässä
(22 arvostelua)
4.7
NashTrump

Hi there, I am an excel vba expert and have been doing this for over 11 years. The project sounds very simple so I can have your program finished in under 2 days providing you are contactable on msn or skype. Pl Lisää

120 $ USD 0 päivässä
(12 arvostelua)
4.4
muhmuuh

Hello! Please check PM for details. Thanks

55 $ USD 2 päivässä
(21 arvostelua)
4.3
ExactSystems

Please see detail on PMB. regards

195 $ USD 5 päivässä
(2 arvostelua)
4.3
JackBradford

Hello, Please see your PMB for details.

240 $ USD 2 päivässä
(9 arvostelua)
3.8
johnsinit

Dear LoftLife! I am an experienced VB and VBA programmer and would be happy to work for you. Please, check PMB. Sincerely yours, John.

68 $ USD 1 päivässä
(11 arvostelua)
3.6
oFAZo

Exceptional quality & Fast work. Please refer to PMB, Profile and Review for details. Thanks FAZ

75 $ USD 5 päivässä
(12 arvostelua)
3.5
refractocd

This can certainly be done using Excel VBA. Please allow some days for communication, timeline difference, etc. I am ready to start IMMEDIATELY. Please refer to PM.

70 $ USD 2 päivässä
(5 arvostelua)
3.1
freelancesol

Good Day, The requirement looks very nice, straight forward and very easy to understand. I am very much confident to deliver this project in time. Since the strategy of doing this project is left open for the develope Lisää

100 $ USD 3 päivässä
(2 arvostelua)
2.4
simonfi

I propose a PHP + MySQL solution which would run on your Mediatemple servers. I currently do full time php and sql development on my day job and have been a professional programmer for over 10 years, as such I feel the Lisää

175 $ USD 2 päivässä
(0 arvostelua)
0.0
yashodharjain

I propose to provide an application developed in MS Excel which would help the user with the following activities: - Data will be imported in Excel worksheet following the guidelines provided - Generation of a R Lisää

135 $ USD 3 päivässä
(0 arvostelua)
0.0
jbruce1982

I have been creating excel macros for about 5 years now for a major corporation. I have MANY examples that I can provide for you. Pick me if you want an honest person who strives to make sure you get exactly what you w Lisää

125 $ USD 3 päivässä
(0 arvostelua)
0.0
pdavid

I'm Excel/Access VBA developer for 12 years. Cheap and quick solution for you. I suggest to use Excel 2007 because of 65000 records limitation in older Excels.

120 $ USD 3 päivässä
(0 arvostelua)
0.0
babushah007

Hi, Please see PM for more details Thanks Abdul

150 $ USD 5 päivässä
(0 arvostelua)
0.0