How can count no of count in cells according to same date but different time shift using macro??

[mars91]

Hi
How can count no of count in cells according to same date but different time shift?? I wanted to complete this by doing macro.

For Example i have 2 time shift :
For Night shift is 1200am to 830am
For Day shift is from 1200pm to 1830pm

For Example,Starting from B2 onward will be the date plus time.
It look something like this : 2011-07-22 5:23
The date are the same but the timing are different.
So if it is 2011-07-22 5:23 then it will be night shift.

So how can i do macro to count the total cells for the Night and Day shift??

I really need help to do this in macro. Any pro,please help me!!

Thank you very much in advance..

 

[mars91]

Hi all,

Anyone can help?? I need help with this in macro.
I cant find any example. Any similar example and codes to try will be good enough for me.

Thanks

 

[Peter_SSs]

Do you really need a macro?
Do these worksheet formulas do what you want?
Both formulas copied down.

Excel Workbook
	B	C	D	E	F	G
1	Date/Time			Date	Night Shift	Day Shift
2	1/08/11 5:51			1/08/11	2	1
3	1/08/11 8:01			2/08/11	0	1
4	1/08/11 15:51			3/08/11	1	1
5	2/08/11 13:20
6	3/08/11 3:00
7	3/08/11 11:58
8	3/08/11 16:44
9
Shifts

 

[T. Valko]

Do you really need a macro?

I asked the same thing:

http://www.mrexcel.com/forum/showthread.php?t=567863

 

[mars91]

Hi,
Peter,thanks for you help. Sorry,I never use this spreadsheet formula before. Can you explain to me how you do it and how it works?? I am abit confused.

To: Peter and T.Valko
I need to do this in macro because this is sort of my mini project so i need to do in macro if it is possible. I am still weak in macro so i am looking for help seeing whether this is possible to be done in macro.

Sorry for any trouble and thank you all.

Please let me know if there are other solutions.

Thanks in advance!

 

[Peter_SSs]

Firstly, please refer to my comment in your other thread about duplicate posts.

Sorry,I never use this spreadsheet formula before. Can you explain to me how you do it ...

To use the formula, you copy it from my post above and paste it into the cell shown.

I need to do this in macro ..

Well, for the layout shown below and your date of interest in cell E2, here's one way.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CountShifts()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> fNight <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = _<br>        "=SUMPRODUCT(--(INT($B$2:$B$#)=$E2),--(MOD($B$2:$B$#,1)<=8.5/24))"<br>    <SPAN style="color:#00007F">Const</SPAN> fDay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = _<br>        "=SUMPRODUCT(--(INT($B$2:$B$#)=$E2),--(MOD($B$2:$B$#,1)>=0.5))"<br>    <br>    lr = Range("B" & Rows.Count).End(xlUp).Row<br>    Range("F2").Formula = Replace(fNight, "#", lr)<br>    Range("G2").Formula = Replace(fDay, "#", lr)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>