I have to report employees total numbers of In time punch that are after 09:00 hours and number of Out time before say 18:00 hours in a month say total 50 employees and for 30 days of a month... Is there a easy way with the help of VBA to achieve this..

my data is like this
01-Aug-17
Employee In_Time Out_Time Minutes
1 9:35 18:00 505
2 9:20 17:58
3 9:15 19:15

Thanks,

Max wrote:

I have to report employees total numbers of In time punch that are after
09:00 hours and number of Out time before say 18:00 hours in a month say
total 50 employees and for 30 days of a month... Is there a easy way
with the help of VBA to achieve this..

my data is like this
01-Aug-17
Employee In_Time Out_Time Minutes
1 9:35 18:00 505
2 9:20 17:58
3 9:15 19:15

Does it need to be VBA? I use a formula like this for a similar purpose:

=([Out_Time]-[In_Time])*1440

If it absolutely must be VBA, use this:

result = (Range("[Out_Time]").Value - Range("[In_Time]").Value) * 1440

In either case, replace [Out_Time] and [In_Time] with appropriate cell
addresses.

Note: This only works if your times don't cross midnight. If they do, this
thread shows how to deal with that (written specifically for my data), but
it's kind of a bitch. Watch the wordwrap:

https://groups.google.com/forum/#!
topic/microsoft.public.excel.worksheet.functions/aDkj8ab8iwY

--
Conversation is not only the vehicle of thought,
it is a tremendous and efficient instrument in thinking.

On Monday, 14 August 2017 04:40:20 UTC+5:30, Auric__ wrote:
Max wrote:

I have to report employees total numbers of In time punch that are after
09:00 hours and number of Out time before say 18:00 hours in a month say
total 50 employees and for 30 days of a month... Is there a easy way
with the help of VBA to achieve this..

my data is like this
01-Aug-17
Employee In_Time Out_Time Minutes
1 9:35 18:00 505
2 9:20 17:58
3 9:15 19:15

Does it need to be VBA? I use a formula like this for a similar purpose:

=([Out_Time]-[In_Time])*1440

If it absolutely must be VBA, use this:

result = (Range("[Out_Time]").Value - Range("[In_Time]").Value) * 1440

In either case, replace [Out_Time] and [In_Time] with appropriate cell
addresses.

Note: This only works if your times don't cross midnight. If they do, this
thread shows how to deal with that (written specifically for my data), but
it's kind of a bitch. Watch the wordwrap:

https://groups.google.com/forum/#!
topic/microsoft.public.excel.worksheet.functions/aDkj8ab8iwY

--
Conversation is not only the vehicle of thought,
it is a tremendous and efficient instrument in thinking.

Thanks Auric__, For your suggested solution.The Formula works fine for the Total Minutes. I also have the trouble in making formula to check weather the In-Time is before 09:00 Hrs or later than the Specific Time i.e 09:00 Hrs and same for the Out_Time as well. Hope I am able to explain my question you.

On Monday, 14 August 2017 12:22:30 UTC+5:30, Max wrote:
On Monday, 14 August 2017 04:40:20 UTC+5:30, Auric__ wrote:
Max wrote:

I have to report employees total numbers of In time punch that are after
09:00 hours and number of Out time before say 18:00 hours in a month say
total 50 employees and for 30 days of a month... Is there a easy way
with the help of VBA to achieve this..

my data is like this
01-Aug-17
Employee In_Time Out_Time Minutes
1 9:35 18:00 505
2 9:20 17:58
3 9:15 19:15

Does it need to be VBA? I use a formula like this for a similar purpose:

=([Out_Time]-[In_Time])*1440

If it absolutely must be VBA, use this:

result = (Range("[Out_Time]").Value - Range("[In_Time]").Value) * 1440

In either case, replace [Out_Time] and [In_Time] with appropriate cell
addresses.

Note: This only works if your times don't cross midnight. If they do, this
thread shows how to deal with that (written specifically for my data), but
it's kind of a bitch. Watch the wordwrap:

https://groups.google.com/forum/#!
topic/microsoft.public.excel.worksheet.functions/aDkj8ab8iwY

--
Conversation is not only the vehicle of thought,
it is a tremendous and efficient instrument in thinking.

Thanks Auric__, For your suggested solution.The Formula works fine for the Total Minutes. I also have the trouble in making formula to check weather the In-Time is before 09:00 Hrs or later than the Specific Time i.e 09:00 Hrs and same for the Out_Time as well. Hope I am able to explain my question you.

Also For Full Month I want to Count Number of 'In_Time' and 'Out_Time' is After and Before the Specific Time. As a Example Total 5 Late In_Time Punch and 3 Early Out Punch in a Month

Hi Max,

Am Mon, 14 Aug 2017 00:09:40 -0700 (PDT) schrieb Max:

Also For Full Month I want to Count Number of 'In_Time' and 'Out_Time' is After and Before the Specific Time. As a Example Total 5 Late In_Time Punch and 3 Early Out Punch in a Month

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgaFO3KiEebzl6vw69w
If your layout is another one, please provide a demo workbook.

Regards
Claus B.
--
Windows10
Office 2016

On Monday, 14 August 2017 17:31:44 UTC+5:30, Claus Busch wrote:
Hi Max,

Am Mon, 14 Aug 2017 00:09:40 -0700 (PDT) schrieb Max:

Also For Full Month I want to Count Number of 'In_Time' and 'Out_Time' is After and Before the Specific Time. As a Example Total 5 Late In_Time Punch and 3 Early Out Punch in a Month

have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgaFO3KiEebzl6vw69w
If your layout is another one, please provide a demo workbook.

Regards
Claus B.
--
Windows10
Office 2016

WOW, Great....Claus, Thanks you made the solution.. Though I need to learn on the Sum Product Function as well as MOD Function..... As I am not clear that why divisor is 3... If you can Explain.

I have to report employees total numbers of In time punch that are after
09:00 hours and number of Out time before say 18:00 hours in a month say
total 50 employees and for 30 days of a month... Is there a easy way with the
help of VBA to achieve this..

my data is like this
01-Aug-17
Employee In_Time Out_Time Minutes
1 9:35 18:00 505
2 9:20 17:58
3 9:15 19:15

Thanks,

This formula works if elapsed time passes midnight...

=IF(AND(Start<"",Stop<""),ROUND(MOD(Stop-Start,1)*24,2),"")

...where 'Start' and 'Stop' are the defined name equivalent of your In_Time and
Out_Time, respectively.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

This formula works if elapsed time passes midnight...

=IF(AND(Start<"",Stop<""),ROUND(MOD(Stop-Start,1)*24,2),"")

..where 'Start' and 'Stop' are the defined name equivalent of your In_Time
and Out_Time, respectively.

Note that this formula is used for project time and so returns hours to 2
decimal places. Use 1440 to return minutes!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Max,

Am Sun, 13 Aug 2017 12:03:08 -0700 (PDT) schrieb Max:

my data is like this
01-Aug-17
Employee In_Time Out_Time Minutes
1 9:35 18:00 505
2 9:20 17:58
3 9:15 19:15

another suggestion:
=IF(COUNT(B2:C2)<2,"",MOD(C2-B2,1)*1440)


Regards
Claus B.
--
Windows10
Office 2016