Count employee who work per hour including midnight, pero paano sa VBA?

[kaitenz]

Hi guys.

Struggle ako sa Excel. Di ko matapos-tapos tong work ko dahil dito.
Ganito ang gusto ko mangyare:

In	Out	# of employees
10:00pm	11:00pm	1
11:00pm	12:00am	2
12:00am	1:00am	2
1:00am	2:00am	2
2:00am	3:00am	1

Employees

Name	Time In	Time Out
Agent A	10:00pm	2:00am
Agent B	11:00pm	3:00am

Bibilangin nya kung ilang empleyado ang nagtatrabaho within sa range ng work time nila. Yung table sa taas ang magandang example sa tanong ko.

Ang problema lang, di ko alam paano ko sisimulan. Wala akong idea pano ko sya gagawin sa VBA. Pahelp naman po kung paano ko sya gagawin sa VBA. Kahit idea lang tapos ako nang bahala sa pagpapatuloy. Thanks po

 

[jskhulitz]

Check columns E, F, and G for solutions. Out of these 3, I suggest to use F / G. Pero nagtataka lang ako nung nagte-test case ako bakit pumapalya yung F column kapag 3 am to 5 am.

Try it by placing 3 am as employee's IN and out records. So kung 3 am ang IN nya, at 3 am din ang OUT nya, di sya nagtrabaho for that hour, so 0.
Next, palitan mo out nya ng 4 am, magiging 1 yung rows ng 3 am to 4 am.
Next, palitan mo out nya ng 5 am, yung columns E and F, naka 0 yung for 4 am to 5 am, pero yung G naka 1 naman.
Pero gawin mo 6 am yung OUT nya, ok naman lahat. Medyo weird lang.

Anyway, si G ang pinaka-consistent.

I attached a sample excel file.

View attachment 330274

 

[kaitenz]

Check columns E, F, and G for solutions. Out of these 3, I suggest to use F / G. Pero nagtataka lang ako nung nagte-test case ako bakit pumapalya yung F column kapag 3 am to 5 am.

Try it by placing 3 am as employee's IN and out records. So kung 3 am ang IN nya, at 3 am din ang OUT nya, di sya nagtrabaho for that hour, so 0.
Next, palitan mo out nya ng 4 am, magiging 1 yung rows ng 3 am to 4 am.
Next, palitan mo out nya ng 5 am, yung columns E and F, naka 0 yung for 4 am to 5 am, pero yung G naka 1 naman.
Pero gawin mo 6 am yung OUT nya, ok naman lahat. Medyo weird lang.

Anyway, si G ang pinaka-consistent.

I attached a sample excel file.

View attachment 1231613

So gagana to pag for example ang hinahanap ko is 11:00pm to 12:00am tapos ang shift nung Agent A is 10:00pm to 7:00am, magka-count pa rin?
Btw, thanks.

 

[jskhulitz]

Nope, di gagana yan sa 2-day span na schedules. Same day schedules pa lang yung nanjan.

 

[kaitenz]

Nope, di gagana yan sa 2-day span na schedules. Same day schedules pa lang yung nanjan.

Ouch. Yun nga ang kailangan ko eh.
Anyway, thanks pa rin sa help mo.

 

[seventhofjune]

TS.. Di aq masyadong marunong magcode e. Pero sinubukan ko gawin. Try mo nga kung pwede na to sayo..

 

[romcel11]

Here you go.

View attachment 330338

Formula:
=SUMPRODUCT((E:E<=$L$2+M3)*(F:F>=$L$2+N3))
GREEN = Select whole Column
VIOLET = ung cell na my date. change mo nlang (ung mismong value ng cell) kpag ibang araw

Explanation:
= $L$2+M3
= [DATE] + [TIME]
= 10/20/2016 + 10:00AM
Excel treats dates as 'double' data type.
Where number 1 is one day from date 01/00/1900 hence 10/20/2016 is 42663 days away from 01/00/1900.
On the other hand, time-values are treated as their respective decimal value.
We know that there are 24 hours in a day, and can easily say that .25 is 6hours after midnight (6:00am).
Now it is safe to assume that '10:00AM of 10/20/2016' is 42663.42 days away from 01/00/1900.

sana nakatulong po