Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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... |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
Thank you all,
Max, are you in human resources dept. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
Thanks
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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) |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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, |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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) |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of 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 |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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" ? |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
Thanks claus
|
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
Oh wow great!!!
|
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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 |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
Thanks Claus, your point is useful.
|
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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. |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparison of Time
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Time comparison not correct | Excel Programming | |||
Time comparison problem | Excel Programming | |||
Time Comparison | Excel Programming | |||
Time comparison formula | Excel Discussion (Misc queries) | |||
Time comparison | Excel Programming |