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.

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

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

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

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 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.

Hi Max,

Am Tue, 15 Aug 2017 01:40:59 -0700 (PDT) schrieb Max:

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.

you want check every 3rd column. Therefore you have to divide the column
number by 3.
MOD(COLUMN(B3:CR3),3)=2
is true for columns 2, 5, 8, 11 and so on.


Regards
Claus B.
--
Windows10
Office 2016

On Tuesday, 15 August 2017 14:38:14 UTC+5:30, Claus Busch wrote:
Hi Max,

Am Tue, 15 Aug 2017 01:40:59 -0700 (PDT) schrieb Max:

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.

you want check every 3rd column. Therefore you have to divide the column
number by 3.
MOD(COLUMN(B3:CR3),3)=2
is true for columns 2, 5, 8, 11 and so on.


Regards
Claus B.
--
Windows10
Office 2016

Thanks Really Appreciates your involvement in resolving my problem...

Thank you all,
Max, are you in human resources dept.

On Tuesday, 15 August 2017 20:53:51 UTC+5:30, TIMOTHY wrote:
Thank you all,
Max, are you in human resources dept.

Not Exactly but The Dept comes under me when the HR Head is out. I am in PMC... Basically Mech. Engr and PMP.

Thanks

Hi all,

I want to take weekly hours min 45 hours as per policy

ie., 5 working days * 9 hours

What would be the formula to count of non-compliance (weekly working hours is below 45 hours)

On Tuesday, 15 August 2017 14:38:14 UTC+5:30, Claus Busch wrote:
Hi Max,

Am Tue, 15 Aug 2017 01:40:59 -0700 (PDT) schrieb Max:

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.

you want check every 3rd column. Therefore you have to divide the column
number by 3.
MOD(COLUMN(B3:CR3),3)=2
is true for columns 2, 5, 8, 11 and so on.


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus, I am sorry to Trouble you again... Please have a look on this and can you tell me where I am Wrong please?

https://1drv.ms/x/s!AtAG_WNGGJX3gQbqM_6963w0n-sl

Thanks & Regards,
Max

On Thursday, 17 August 2017 18:16:18 UTC+5:30, Max wrote:
On Tuesday, 15 August 2017 14:38:14 UTC+5:30, Claus Busch wrote:
Hi Max,

Am Tue, 15 Aug 2017 01:40:59 -0700 (PDT) schrieb Max:

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.

you want check every 3rd column. Therefore you have to divide the column
number by 3.
MOD(COLUMN(B3:CR3),3)=2
is true for columns 2, 5, 8, 11 and so on.


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus, I am sorry to Trouble you again... Please have a look on this and can you tell me where I am Wrong please?

https://1drv.ms/x/s!AtAG_WNGGJX3gQbqM_6963w0n-sl

Also My Time is copy pasted from a Text....The Time format is in Text form from the Punch Machine output file and I my formula also puts value as text.... If I will change the Full selection to Time Format it doesn't do the same but after words I have to select each and every cell and then Press 'F2' and 'Enter' and then the Format changes to the desired one... Even the Conditional formatting also works after I select and change every cell...

Is there any bug in my system or it's normal behavior of the Excel?

Thanks & Regards,
Max

Hi Max,

Am Thu, 17 Aug 2017 06:04:03 -0700 (PDT) schrieb Max:

Also My Time is copy pasted from a Text....The Time format is in Text form from the Punch Machine output file and I my formula also puts value as text... If I will change the Full selection to Time Format it doesn't do the same but after words I have to select each and every cell and then Press 'F2' and 'Enter' and then the Format changes to the desired one... Even the Conditional formatting also works after I select and change every cell...

1) All times that look like hh:mm:ss are text values. Change them to
numbers with Text to columns or with multiplying by 1.
2) You get #NV if you have error values in your table. I deleted the
error values and the formula works.

https://1drv.ms/x/s!AqMiGBK2qniTgaFXm0VyX0L-MhDh9Q


Regards
Claus B.
--
Windows10
Office 2016

On Thursday, 17 August 2017 18:48:19 UTC+5:30, Claus Busch wrote:
Hi Max,

Am Thu, 17 Aug 2017 06:04:03 -0700 (PDT) schrieb Max:

Also My Time is copy pasted from a Text....The Time format is in Text form from the Punch Machine output file and I my formula also puts value as text... If I will change the Full selection to Time Format it doesn't do the same but after words I have to select each and every cell and then Press 'F2' and 'Enter' and then the Format changes to the desired one... Even the Conditional formatting also works after I select and change every cell...

1) All times that look like hh:mm:ss are text values. Change them to
numbers with Text to columns or with multiplying by 1.
2) You get #NV if you have error values in your table. I deleted the
error values and the formula works.

https://1drv.ms/x/s!AqMiGBK2qniTgaFXm0VyX0L-MhDh9Q


Regards
Claus B.
--
Windows10
Office 2016

Thank You Very much Dear and heartily appreciate your prompt reply on solving my problem...
Regards,

Hi Max,

Am Thu, 17 Aug 2017 06:50:39 -0700 (PDT) schrieb Max:

Thank You Very much Dear and heartily appreciate your prompt reply on solving my problem...

you are welcome.
Have another look in OneDrive. I changed all text values to numbers.


Regards
Claus B.
--
Windows10
Office 2016

Hi,

Any of you have solution to my query.

My query is every employee need to complete 45 hours a week (5*9), irrespective of whether logged in after 9 am or before 6 pm as some location employee have flexible time policy where employee can come anytime before 11 am and need to complete 9 hours

Also while calculating 45 hours per week if any Leave or outdoor would be there how we can adjust the formula as per number of days (n*9, where n €¹=5)

Hi Timothy,

Am Thu, 17 Aug 2017 09:57:29 -0700 (PDT) schrieb TIMOTHY:

My query is every employee need to complete 45 hours a week (5*9), irrespective of whether logged in after 9 am or before 6 pm as some location employee have flexible time policy where employee can come anytime before 11 am and need to complete 9 hours

Also while calculating 45 hours per week if any Leave or outdoor would be there how we can adjust the formula as per number of days (n*9, where n ?=5)

we can't provide a formula because we don't know the layout of your
data.


Regards
Claus B.
--
Windows10
Office 2016

On Thursday, 17 August 2017 18:16:18 UTC+5:30, Max wrote:
On Tuesday, 15 August 2017 14:38:14 UTC+5:30, Claus Busch wrote:
Hi Max,

Am Tue, 15 Aug 2017 01:40:59 -0700 (PDT) schrieb Max:

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.

you want check every 3rd column. Therefore you have to divide the column
number by 3.
MOD(COLUMN(B3:CR3),3)=2
is true for columns 2, 5, 8, 11 and so on.


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus, I am sorry to Trouble you again... Please have a look on this and
can you tell me where I am Wrong please?

https://1drv.ms/x/s!AtAG_WNGGJX3gQbqM_6963w0n-sl

Also My Time is copy pasted from a Text....The Time format is in Text form
from the Punch Machine output file and I my formula also puts value as
text... If I will change the Full selection to Time Format it doesn't do the
same but after words I have to select each and every cell and then Press 'F2'
and 'Enter' and then the Format changes to the desired one... Even the
Conditional formatting also works after I select and change every cell...

Is there any bug in my system or it's normal behavior of the Excel?

Thanks & Regards,
Max

Why copy/paste when you can read the file via standard VBA I/O functions and
convert from text while populating the time cells?? In this day and age where
time is so much in demand for the workload we have on our plate, it just makes
sense to automate repetitive tasks to the nines so we yield a higher level of
productivity!!!

--
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,
Please find below report which I get from HR portal

Employee no. 01-Aug-17 02-Aug-17 03-Aug-17 04-Aug-17 05-Aug-17 06-Aug-17
1 P P OD P WO WO
2 P P P GL WO WO
3 P P GL P WO WO


P for present
A for Absent
OD for outdoor
GL for general leave
WO for weekly off

In another report I would be getting date with time

On Thursday, 17 August 2017 19:23:36 UTC+5:30, Claus Busch wrote:
Hi Max,

Am Thu, 17 Aug 2017 06:50:39 -0700 (PDT) schrieb Max:

Thank You Very much Dear and heartily appreciate your prompt reply on solving my problem...

you are welcome.
Have another look in OneDrive. I changed all text values to numbers.


Regards
Claus B.
--
Windows10
Office 2016

Thanks Clause....for your help as now the reporting from HR is much easier now on... Cheers

On Monday, 21 August 2017 12:46:15 UTC+5:30, Max wrote:
On Thursday, 17 August 2017 19:23:36 UTC+5:30, Claus Busch wrote:
Hi Max,

Am Thu, 17 Aug 2017 06:50:39 -0700 (PDT) schrieb Max:

Thank You Very much Dear and heartily appreciate your prompt reply on solving my problem...

you are welcome.
Have another look in OneDrive. I changed all text values to numbers.


Regards
Claus B.
--
Windows10
Office 2016

Thanks Clause....for your help as now the reporting from HR is much easier now on... One More Question (As I am more interested in Excel and Learning the Formulas... Is there any way to retrieve only time from the full date and time value... i.e I want to retrieve only hh:mm "10:50" from full value like dd-mm-yyyy hh:mm:ss "07-08-1970 10:50:00" ?

Hi Max,

Am Mon, 21 Aug 2017 03:26:57 -0700 (PDT) schrieb Max:

One More Question (As I am more interested in Excel and Learning the Formulas... Is there any way to retrieve only time from the full date and time value... i.e I want to retrieve only hh:mm "10:50" from full value like dd-mm-yyyy hh:mm:ss "07-08-1970 10:50:00" ?

your value in A1:

With =INT(A1) you get the date
With =MOD(A1,1) you get the time.


Regards
Claus B.
--
Windows10
Office 2016

Thanks claus

Oh wow great!!!

Hi all,

I have employee attendance file as follows (access card entry at different doors or may be in the same door multiple times in company premises)

I want to only consider in time (first entry) & out time (last entry)

Given example of one date. Please suggest formula for this.

Next date would be below this & same logic like this.

Local Time
7/3/2017 9:02:47 AM
7/3/2017 9:06:16 AM
7/3/2017 10:41:11 AM
7/3/2017 11:51:03 AM
7/3/2017 12:59:55 PM
7/3/2017 2:13:45 PM
7/3/2017 2:50:31 PM
7/3/2017 4:23:39 PM
7/3/2017 4:23:50 PM
7/3/2017 4:29:37 PM
7/3/2017 4:33:22 PM
7/3/2017 5:56:57 PM
7/3/2017 6:04:10 PM

Hi,

Am Fri, 1 Sep 2017 23:03:58 -0700 (PDT) schrieb TIMOTHY:

I want to only consider in time (first entry) & out time (last entry)
Next date would be below this & same logic like this.

Local Time
7/3/2017 9:02:47 AM
7/3/2017 9:06:16 AM
7/3/2017 10:41:11 AM
7/3/2017 11:51:03 AM
7/3/2017 12:59:55 PM
7/3/2017 2:13:45 PM
7/3/2017 2:50:31 PM
7/3/2017 4:23:39 PM
7/3/2017 4:23:50 PM
7/3/2017 4:29:37 PM
7/3/2017 4:33:22 PM
7/3/2017 5:56:57 PM
7/3/2017 6:04:10 PM

try:
=MAX(IF(INT(A2:A100)=INT(A2),A2:A100))-A2
and insert the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016

Thank you, I have solved this using incremental count, if I would have been know this would have finished my work early.

Thank you once again

Thanks Claus, your point is useful.

Hi,

Suppose I have attendance timing in one sheet employee wise & I want summary of who are late comers that is after 10 am.

Using sumproduct am getting that, but employee Ids are not the same sorting as in sheet1.

Which formula would be useful.

Hi Claus,

I have seen the formula in one-story & applied the same to my data, but the problem is it counts blank cells also.

For example if total working days in a month is 22 & if an employee left the org. in middle of the month say 20 days he worked.

While counting whether he completed 9 hours it's showing 22. It is calculating blank also.