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

Panoos64 · May 7, 2019

How to insert the missing dates base on col. "C"? Any support is much appreciated. Thank you all in advance

elynoy · May 7, 2019

you want a work schedule? input the time the worked starts the shift and finishs it and give the amount of time worked? with the total of hours in the month?

Repush · May 7, 2019

Some questions . .
What did you try already?
Why VBA?
Why not vlookups from a sheet containing all workdays?

elynoy · May 7, 2019

here. check it and tell if is this what you're after. Watch out for the formulas so you dont delete anything important.
https://uploadfiles.io/frjkgs6w

There's a DATE behind the button (CELL B2) if you're curious about how it works. Check the controle on the button itself too.

eLy

Panoos64 · May 8, 2019

Yes elyn, i need a staff timetable. Therefore that firstly i should find the days "OFF" which the machine doesn't appear and is the reason that i require a VBA code. Note that the employees arises around 2000 and then i should turn the names and their working days on horizontal lines below each other. I studied your template which i found it so interesting but unfortunately for me i couldn't find the solution to adapt it on my data. The main issue is to insert the rows with dates which the employee did not register working time time (IN & Out) However thank you so much for your support. Have a nice day

Panoos64 · May 8, 2019

Hi Repush, I did not try anything, just i was searching to find a code so that to adapt it on my data.
I think a VBA code is going to be the best solution for my project. Due to years month/days, Jan 31, Feb 28, Mar 31 and so on... i suppose that, is so complicate to use vlookup, but let me think about maybe i will find a solution. However any suggestion is much appreciate. Thank you for your support and your intention to resolve my project. Have a great day

elynoy · May 8, 2019

I'm sorry, I'm having touble to understand your question.

you want a work schedule for 2000 employee? what do you mean by "full calendar" the exemple I gave you can be tweeked to host every month of the year like it is, selecting each month or add all the month below each other how many times you need/want.

and the "days off" can be used like COUNTBLANK for the cells where there are no in/out records.

Check this exemple for better understanding:
https://ufile.io/p97gsvt5

eLy

Panoos64 · May 8, 2019

Hi elynoy, please note that the above is an extract for one employee. Therefore that for 1st employee i need its calendar e.g. for January 2019 should start 01/01/2019 till 31/01/2019 for 2nd employees the same calendar and so on...in vertical form till the last employee. Note that the spreadsheet's rows are around 2500 - 3000 include empty rows. The above spreadsheet cannot adapt it to my data because as i mentioned i need for each employee its calendar. Thank you once again for your support

elynoy · May 8, 2019

Can you send me an exemple of your sheet?

eLy

Complete calendar month's dates

Panoos64

Well-known Member

Excel Facts

Panoos64

Well-known Member

elynoy

Board Regular

Repush

Board Regular

elynoy

Board Regular

Panoos64

Well-known Member

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

Excel Facts

Well-known Member

Board Regular

Board Regular

Board Regular

Well-known Member

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