Complete calendar month's dates

Panoos64 · May 6, 2019

Hi to all of you, kindly require to provide me support for a VBA code so that, complete the month’s calendar dates in col. “C”, where an employee have no clock time records and thereafter to place in adjacent cell in col. “P” the letter “O” which it means that on that date he/she was day “OFF” Therefore that the row “1” is heading and below of “Total” of each in col. “A” there is a blank row. I present below original data and expected result. Thank you all in advance

Original data

	A	B	C	D	E	P
1	NO	Employee	Date	IN	OUT	Total Time
	5051	EMPLOYEE 5051	02/04/2019	09:00:28	17:07:52	08:07:24
2	5051	EMPLOYEE 5051	03/04/2019	08:31:36	17:27:39	08:56:03
3	5051	EMPLOYEE 5051	04/04/2019	09:03:03	17:12:48	08:09:45
	5051	EMPLOYEE 5051	05/04/2019	08:55:20	16:27:47	07:32:27
4	5051	EMPLOYEE 5051	06/04/2019	08:30:00	15:30:00	07:00:00
5	5051	EMPLOYEE 5051	08/04/2019	08:44:55	17:07:31	08:22:36
	5051	EMPLOYEE 5051	09/04/2019	08:19:36	17:12:31	08:52:55
6	5051	EMPLOYEE 5051	10/04/2019	08:40:35	17:08:54	08:28:19
7	5051	EMPLOYEE 5051	11/04/2019	08:15:29	17:00:32	08:45:03
	5051	EMPLOYEE 5051	12/04/2019	09:00:50	17:14:48	08:13:58
8	5051	EMPLOYEE 5051	15/04/2019	07:55:13	16:54:10	08:58:57
9	5051	EMPLOYEE 5051	16/04/2019	08:47:21	17:22:18	08:34:57
	5051	EMPLOYEE 5051	17/04/2019	08:06:59	17:07:26	09:00:27
10	5051	EMPLOYEE 5051	18/04/2019	08:15:21	16:59:21	08:44:00
11	5051	EMPLOYEE 5051	19/04/2019	08:07:39	16:18:13	08:10:34
	5051	EMPLOYEE 5051	22/04/2019	08:53:18	17:13:00	08:19:42
12	5051	EMPLOYEE 5051	23/04/2019	08:30:40	17:18:15	08:47:35
13	5051	EMPLOYEE 5051	24/04/2019	08:30:14	16:24:01	07:53:47
	5051	EMPLOYEE 5051	25/04/2019	07:33:00	16:46:53	09:13:53
14	5051 Count					19
15	5051 Total					160:12:22
16

Expected result

	A	B	C	D	E	P
1	NO	Employee	Date	IN	OUT	Total Time
2	5050	EMPLOYEE 5051	01/04/2019			O
3	5051	EMPLOYEE 5051	02/04/2019	09:00:28	17:07:52	08:07:24
4	5051	EMPLOYEE 5051	03/04/2019	08:31:36	17:27:39	08:56:03
5	5051	EMPLOYEE 5051	04/04/2019	09:03:03	17:12:48	08:09:45
6	5051	EMPLOYEE 5051	05/04/2019	08:55:20	16:27:47	07:32:27
7	5051	EMPLOYEE 5051	06/04/2019	08:30:00	15:30:00	07:00:00
8	5051	EMPLOYEE 5051	07/04/2019			O
9	5051	EMPLOYEE 5051	08/04/2019	08:44:55	17:07:31	08:22:36
10	5051	EMPLOYEE 5051	09/04/2019	08:19:36	17:12:31	08:52:55
11	5051	EMPLOYEE 5051	10/04/2019	08:40:35	17:08:54	08:28:19
12	5051	EMPLOYEE 5051	11/04/2019	08:15:29	17:00:32	08:45:03
13	5051	EMPLOYEE 5051	12/04/2019	09:00:50	17:14:48	08:13:58
14	5051	EMPLOYEE 5051	13/04/2019			O
15	5051	EMPLOYEE 5051	14/04/2019			O
16	5051	EMPLOYEE 5051	15/04/2019	07:55:13	16:54:10	08:58:57
17	5051	EMPLOYEE 5051	16/04/2019	08:47:21	17:22:18	08:34:57
18	5051	EMPLOYEE 5051	17/04/2019	08:06:59	17:07:26	09:00:27
19	5051	EMPLOYEE 5051	18/04/2019	08:15:21	16:59:21	08:44:00
20	5051	EMPLOYEE 5051	19/04/2019	08:07:39	16:18:13	08:10:34
21	5051	EMPLOYEE 5051	20/04/2019			O
22	5051	EMPLOYEE 5051	21/04/2019			O
23	5051	EMPLOYEE 5051	22/04/2019	08:53:18	17:13:00	08:19:42
24	5051	EMPLOYEE 5051	23/04/2019	08:30:40	17:18:15	08:47:35
25	5051	EMPLOYEE 5051	24/04/2019	08:30:14	16:24:01	07:53:47
26	5051	EMPLOYEE 5051	25/04/2019	07:33:00	16:46:53	09:13:53
27	5051	EMPLOYEE 5051	26/04/2019			O
28	5051	EMPLOYEE 5051	27/04/2019			O
29	5051	EMPLOYEE 5051	28/04/2019			O
30	5051	EMPLOYEE 5051	29/04/2019			O
31	5051	EMPLOYEE 5051	30/04/2019			O
32	5051 Count					19
33	5051 Total					160:12:22
34

elynoy · May 8, 2019

You need it for 2000 to 3000 people? Create one of those sheets for each employee? I think I cant help more that that. getting out of my league now.

Best regards,
eLy

Panoos64 · May 13, 2019

OK Ely, I am on holiday and I will sent it during this week. I am sorry. Just today I saw your post. Kind regards

Panoos64 · May 16, 2019

Hi ely, please see below an extract of 2 employees. I would like to note that the spreadsheet contains around 2000 rows and not 2000 employees. Therefore that the code should run through col. "C" and should insert rows and date for missing dates. Kindly ignore that i mentioned to place the "O" in col. "P" and i prefer it to be blank. Due to lot of data i couldn't write all the file, but is similar for any employee till last fulfill row. However the rows are not more than 2500. Thank you once again for your support. Have a nice day

	A	B	C	D	E	P
1	NO	Employee	Date	IN	OUT	Total Time
2	5051	EMPLOYEE 5051	02/04/2019	09:00:28	17:07:52	08:07:24
3	5051	EMPLOYEE 5051	03/04/2019	08:31:36	17:27:39	08:56:03
4	5051	EMPLOYEE 5051	04/04/2019	09:03:03	17:12:48	08:09:45
5	5051	EMPLOYEE 5051	05/04/2019	08:55:20	16:27:47	07:32:27
6	5051	EMPLOYEE 5051	06/04/2019	08:30:00	15:30:00	07:00:00
7	5051	EMPLOYEE 5051	08/04/2019	08:44:55	17:07:31	08:22:36
8	5051	EMPLOYEE 5051	09/04/2019	08:19:36	17:12:31	08:52:55
9	5051	EMPLOYEE 5051	10/04/2019	08:40:35	17:08:54	08:28:19
10	5051	EMPLOYEE 5051	11/04/2019	08:15:29	17:00:32	08:45:03
11	5051	EMPLOYEE 5051	12/04/2019	09:00:50	17:14:48	08:13:58
12	5051	EMPLOYEE 5051	15/04/2019	07:55:13	16:54:10	08:58:57
13	5051	EMPLOYEE 5051	16/04/2019	08:47:21	17:22:18	08:34:57
14	5051	EMPLOYEE 5051	17/04/2019	08:06:59	17:07:26	09:00:27
15	5051	EMPLOYEE 5051	18/04/2019	08:15:21	16:59:21	08:44:00
16	5051	EMPLOYEE 5051	19/04/2019	08:07:39	16:18:13	08:10:34
17	5051	EMPLOYEE 5051	22/04/2019	08:53:18	17:13:00	08:19:42
18	5051	EMPLOYEE 5051	23/04/2019	08:30:40	17:18:15	08:47:35
19	5051	EMPLOYEE 5051	24/04/2019	08:30:14	16:24:01	07:53:47
20	5051	EMPLOYEE 5051	25/04/2019	07:33:00	16:46:53	09:13:53
21	5051 Count					19
22	5051 Total					160:12:22
23
24	5062	EMPLOYEE 5062	01/04/2019	08:01:43	16:49:59	08:48:16
25	5062	EMPLOYEE 5062	03/04/2019	08:13:11	16:44:41	08:31:30
26	5062	EMPLOYEE 5062	04/04/2019	08:07:31	17:00:47	08:53:16
27	5062	EMPLOYEE 5062	05/04/2019	08:00:25	16:35:10	08:34:45
28	5062	EMPLOYEE 5062	06/04/2019	08:13:28	16:55:04	08:41:36
29	5062	EMPLOYEE 5062	08/04/2019	08:03:11	17:01:50	08:58:39
30	5062	EMPLOYEE 5062	09/04/2019	07:54:23	16:44:39	08:50:16
31	5062	EMPLOYEE 5062	10/04/2019	07:51:37	16:45:47	08:54:10
32	5062	EMPLOYEE 5062	11/04/2019	08:01:16	16:55:01	08:53:45
33	5062	EMPLOYEE 5062	12/04/2019	07:59:41	16:48:07	08:48:26
34	5062	EMPLOYEE 5062	13/04/2019	08:01:58	16:52:13	08:50:15
35	5062	EMPLOYEE 5062	15/04/2019	07:57:05	16:22:58	08:25:53
36	5062	EMPLOYEE 5062	16/04/2019	08:03:17	16:46:51	08:43:34
37	5062	EMPLOYEE 5062	17/04/2019	07:53:42	17:01:15	09:07:33
38	5062	EMPLOYEE 5062	18/04/2019	08:01:50	16:42:14	08:40:24
39	5062	EMPLOYEE 5062	19/04/2019	08:20:02	17:07:43	08:47:41
40	5062	EMPLOYEE 5062	22/04/2019	08:07:17	16:38:33	08:31:16
41	5062	EMPLOYEE 5062	23/04/2019	07:57:31	16:42:40	08:45:09
42	5062	EMPLOYEE 5062	24/04/2019	08:30:14	16:24:01	07:53:47
43	5062	EMPLOYEE 5062	25/04/2019	07:33:00	16:46:53	09:13:53
44	5062	EMPLOYEE 5062	26/04/2019	08:35:01	16:45:12	08:10:11
45	5062	EMPLOYEE 5062	27/04/2019	08:10:28	17:05:10	08:54:42
46	5062	EMPLOYEE 5062	30/04/2019	08:05:15	16:50:18	08:45:03
47	5062 Count					23
48	5062 Total					200:44:00

elynoy · May 17, 2019

ok, I gave it another shot.
If this is not what you are looking for I cant help you.

Note that, each sheet is one Month, I made January, February and March. For the rest you just need to copy and paste the full sheet and then choose another MONTH at the top and the month changes for ALL the month in the sheet where the month changes.

Also, you can add more months in every sheet to suit how many employees you need more or delete if you dont need that many.

give it a test at:
https://ufile.io/vb1uokux

eLy

Panoos64 · May 17, 2019

Thank u ely, ok, i will attempt to transfer my file's data into your template you sent me. Thank you so much for your support and your assistant to resolve my issue and i express my apologies for such complicate project. Have a great day!

elynoy · May 17, 2019

Sorry use this one instead. I forgot to change the format cells to hh:mm:ss. it's the same only with that difference since it has the *24 in the calculations.

https://ufile.io/y410nrv0

best of luck
eLy

Panoos64 · May 17, 2019

Thank you ely for noticing and providing the above. Is the format for accurate hours calculation. All the best

elynoy · May 17, 2019

No problem. Hope you can achieve what you need.

eLy

Complete calendar month's dates

Panoos64

Well-known Member

elynoy

Board Regular

Excel Facts

Panoos64

Well-known Member

Panoos64

Well-known Member

elynoy

Board Regular

Panoos64

Well-known Member

elynoy

Board Regular

Panoos64

Well-known Member

elynoy

Board Regular

Similar threads

Forum statistics

Share this page

Complete calendar month's dates

Well-known Member

Board Regular

Excel Facts

Well-known Member

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock