Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #110170 > unrolled thread

Comparison of Time

Started byMax <mukeshoza99@gmail.com>
First post2017-08-13 12:03 -0700
Last post2017-08-14 08:16 +0200
Articles 14 on this page of 34 — 5 participants

Back to article view | Back to microsoft.public.excel.programming

Contents

  Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-13 12:03 -0700
    Re: Comparison of Time "Auric__" <not.my.real@email.address> - 2017-08-13 23:05 +0000
      Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-13 23:52 -0700
        Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-14 00:09 -0700
          Re: Comparison of Time Claus Busch <claus_busch@t-online.de> - 2017-08-14 14:01 +0200
            Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-15 01:40 -0700
              Re: Comparison of Time Claus Busch <claus_busch@t-online.de> - 2017-08-15 11:08 +0200
                Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-15 04:21 -0700
                  Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-08-15 08:23 -0700
                    Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-15 22:35 -0700
                      Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-08-16 05:46 -0700
                Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-17 05:46 -0700
                  Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-17 06:04 -0700
                    Re: Comparison of Time Claus Busch <claus_busch@t-online.de> - 2017-08-17 15:18 +0200
                      Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-17 06:50 -0700
                        Re: Comparison of Time Claus Busch <claus_busch@t-online.de> - 2017-08-17 15:53 +0200
                          Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-08-17 09:57 -0700
                            Re: Comparison of Time Claus Busch <claus_busch@t-online.de> - 2017-08-17 19:00 +0200
                              Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-08-18 22:07 -0700
                          Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-21 00:16 -0700
                            Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-21 03:26 -0700
                              Re: Comparison of Time Claus Busch <claus_busch@t-online.de> - 2017-08-21 12:34 +0200
                                Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-08-21 07:58 -0700
                                Re: Comparison of Time Max <mukeshoza99@gmail.com> - 2017-08-21 22:24 -0700
                                  Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-09-01 23:03 -0700
                                    Re: Comparison of Time Claus Busch <claus_busch@t-online.de> - 2017-09-02 09:12 +0200
                                      Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-09-02 11:31 -0700
                      Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-09-04 19:09 -0700
                        Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-09-04 20:18 -0700
                          Re: Comparison of Time TIMOTHY <jey.kumar87@gmail.com> - 2017-09-05 02:52 -0700
                    Re: Comparison of Time GS <gs@v.invalid> - 2017-08-17 19:19 -0400
    Re: Comparison of Time GS <gs@v.invalid> - 2017-08-13 20:18 -0400
      Re: Comparison of Time GS <gs@v.invalid> - 2017-08-14 07:27 -0400
    Re: Comparison of Time Claus Busch <claus_busch@t-online.de> - 2017-08-14 08:16 +0200

Page 2 of 2 — ← Prev page 1 [2]

#110208

FromMax <mukeshoza99@gmail.com>
Date2017-08-21 03:26 -0700
Message-ID<3952fc46-c8df-4492-bca5-4696134cfc45@googlegroups.com>
In reply to#110207
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" ?

[toc] | [prev] | [next] | [standalone]

#110209

FromClaus Busch <claus_busch@t-online.de>
Date2017-08-21 12:34 +0200
Message-ID<oneco3$qli$1@dont-email.me>
In reply to#110208
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

[toc] | [prev] | [next] | [standalone]

#110210

FromTIMOTHY <jey.kumar87@gmail.com>
Date2017-08-21 07:58 -0700
Message-ID<6dd83c00-ad7a-4a82-bbf0-8e89a8067ccc@googlegroups.com>
In reply to#110209
Thanks claus

[toc] | [prev] | [next] | [standalone]

#110211

FromMax <mukeshoza99@gmail.com>
Date2017-08-21 22:24 -0700
Message-ID<8fc62f4b-5c19-4c74-b7de-7072fee6def4@googlegroups.com>
In reply to#110209
Oh wow great!!!

[toc] | [prev] | [next] | [standalone]

#110246

FromTIMOTHY <jey.kumar87@gmail.com>
Date2017-09-01 23:03 -0700
Message-ID<8b16500c-6ba8-4696-8182-69ecdce763fe@googlegroups.com>
In reply to#110211
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

[toc] | [prev] | [next] | [standalone]

#110248

FromClaus Busch <claus_busch@t-online.de>
Date2017-09-02 09:12 +0200
Message-ID<oodlcl$s9j$1@dont-email.me>
In reply to#110246
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

[toc] | [prev] | [next] | [standalone]

#110249

FromTIMOTHY <jey.kumar87@gmail.com>
Date2017-09-02 11:31 -0700
Message-ID<e0a66e9d-463b-4691-b0bd-cec0385440e0@googlegroups.com>
In reply to#110248
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

[toc] | [prev] | [next] | [standalone]

#110251

FromTIMOTHY <jey.kumar87@gmail.com>
Date2017-09-04 19:09 -0700
Message-ID<7bb8068c-2fa4-4fad-93ab-ed1c3b3a5361@googlegroups.com>
In reply to#110199
Thanks Claus, your point is useful.

[toc] | [prev] | [next] | [standalone]

#110252

FromTIMOTHY <jey.kumar87@gmail.com>
Date2017-09-04 20:18 -0700
Message-ID<ed4d76b2-5f56-4900-91a7-53676c3ee8aa@googlegroups.com>
In reply to#110251
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.

[toc] | [prev] | [next] | [standalone]

#110253

FromTIMOTHY <jey.kumar87@gmail.com>
Date2017-09-05 02:52 -0700
Message-ID<63a8a633-b945-4935-b8e6-eb55b1800926@googlegroups.com>
In reply to#110252
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.

[toc] | [prev] | [next] | [standalone]

#110204

FromGS <gs@v.invalid>
Date2017-08-17 19:19 -0400
Message-ID<on582e$nji$1@dont-email.me>
In reply to#110198
> 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

[toc] | [prev] | [next] | [standalone]

#110172

FromGS <gs@v.invalid>
Date2017-08-13 20:18 -0400
Message-ID<omqq02$hcu$1@dont-email.me>
In reply to#110170
> 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

[toc] | [prev] | [next] | [standalone]

#110176

FromGS <gs@v.invalid>
Date2017-08-14 07:27 -0400
Message-ID<oms17m$euk$1@dont-email.me>
In reply to#110172
>
> 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

[toc] | [prev] | [next] | [standalone]

#110173

FromClaus Busch <claus_busch@t-online.de>
Date2017-08-14 08:16 +0200
Message-ID<omrf0e$tm9$1@dont-email.me>
In reply to#110170
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

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | microsoft.public.excel.programming

csiph-web