Date format calculation

Date format calculation

Post by Deba Ranja » Sun, 03 Jun 2012 16:09:04



Dear Experts,

    i have again got confusion in date and time format calculation, Please
see the attached file, i have elaborate the details in the sheet. Please
help in calculating the problem.

Thanks & Regards,
*Deba Ranjan P*

****

  date format.xlsx
15K Download
 
 
 

Date format calculation

Post by Rajan_Verm » Mon, 04 Jun 2012 16:58:46


Hi

What do you mean by this Login time : "879:55:64" is it 879 Hour 55 Minute
and 64 Seconds??

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of Deba Ranjan
Sent: 02 June 2012 12:39

Subject: $$Excel-Macros$$ Date format calculation

Dear Experts,

    i have again got confusion in date and time format calculation, Please
see the attached file, i have elaborate the details in the sheet. Please
help in calculating the problem.

Thanks & Regards,
Deba Ranjan P

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to


 
 
 

Date format calculation

Post by Deba Ranja » Tue, 05 Jun 2012 12:57:21


Yes.......exactly.

Thanks & Regards,
*Deba Ranjan P*

****


> Hi****

> What do you mean by this Login time : 879:55:64 is it 879 Hour 55
> Minute and 64 Seconds??****

> ** **

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*

> ** **



> *Sent:* 02 June 2012 12:39

> *Subject:* $$Excel-Macros$$ Date format calculation****

> ** **

> Dear Experts,

>     i have again got confusion in date and time format calculation, Please
> see the attached file, i have elaborate the details in the sheet. Please
> help in calculating the problem.

>   ****

>  ****

> Thanks & Regards,
> *Deba Ranjan P*****

> ** **

> ** **

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


 
 
 

Date format calculation

Post by Deba Ranja » Tue, 05 Jun 2012 13:31:32


Please have a look on this Dear Experts.

Thanks & Regards,
*Deba Ranjan P*

****


> Yes.......exactly.

> Thanks & Regards,
> *Deba Ranjan P*

> ****


>> Hi****

>> What do you mean by this Login time : 879:55:64 is it 879 Hour 55
>> Minute and 64 Seconds??****

>> ** **

>> * *

>> *Regards*

>> *Rajan verma*

>> *+91 7838100659 [IM-Gtalk]*

>> ** **



>> *Sent:* 02 June 2012 12:39

>> *Subject:* $$Excel-Macros$$ Date format calculation****

>> ** **

>> Dear Experts,

>>     i have again got confusion in date and time format calculation,
>> Please see the attached file, i have elaborate the details in the sheet.
>> Please help in calculating the problem.

>>   ****

>>  ****

>> Thanks & Regards,
>> *Deba Ranjan P*****

>> ** **

>> ** **

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------

>> To unsubscribe, send a blank email to


 
 
 

Date format calculation

Post by Asa Rossof » Tue, 05 Jun 2012 16:52:08


Hi Deba,

For the Login Time column, the reason SUM is not working properly is that
many of the login times are being interpreted as text instead of as
[h]:mm:ss entries.

1)      For the login time column with a custom format:

[h]:mm:ss

or the corresponding preset "Time" format:

37:30:55

2)      Then, only enter valid times.  Your problem entries all have minutes
or seconds > 59, and Excel knows that this could not indicate time properly,
so treats it as text.  Correct the following entries:

879:55:64

899:54:78

98:54:87

87:54:78

198:89:55

3)      Optionally remove cell justification.  You have the cells
left-justifed.  If you remove justification, correct times will be
right-justified, and incorrect ones will be interpreted as text and
therefore left-justified.  This helps in noticing a problem early on.

4)     Get rid of the TEXT function in your total formula (the cell format
takes care of formatting), and simply use:

=SUM(B2:B8)

For the Total login cell in the summary area (F3), simply refer to the total
in B9 directly:

=B9

For the No of days cell (F4):

a.      If you need the number of 24-hour periods for the Total login time
use:

=B9

and format the cell as a number (it will default to time format).  The
result will be in terms of days, since that is how Excel stores dates and
times.

b.      If you need the number of periods of length "1 day hour" (the number
in F1), use:

=B9*24/F1

(and format the cell as a number)

For the Login time for 1 day. cell (F5), I don't really understand the
formula or what you want, but perhaps the formula is correct and you just
need to format the result for time:

[h]:mm:ss

Asa


On Behalf Of Deba Ranjan
Sent: Saturday, June 02, 2012 12:09 AM

Subject: $$Excel-Macros$$ Date format calculation

Dear Experts,

    i have again got confusion in date and time format calculation, Please
see the attached file, i have elaborate the details in the sheet. Please
help in calculating the problem.

Thanks & Regards,
Deba Ranjan P

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Date format calculation

Post by Deba Ranja » Tue, 05 Jun 2012 17:23:40


Dear Asa,

   Can you please explain in the sheet itself.

Thanks & Regards,
*Deba Ranjan P*

****


> Hi Deba,****

> ** **

> *For the Login Time column,* the reason SUM is not working properly is
> that many of the login times are being interpreted as text instead of as
> [h]:mm:ss entries.****

> **1)      **For the login time column with a custom format:****

> [h]:mm:ss****

> or the corresponding preset "Time" format:****

> 37:30:55****

> **2)      **Then, only enter valid times.  Your problem entries all have
> minutes or seconds > 59, and Excel knows that this could not indicate time
> properly, so treats it as text.  Correct the following entries:****

> 879:55:64****

> 899:54:78****

> 98:54:87****

> 87:54:78****

> 198:89:55****

> **3)      **Optionally remove cell justification.  You have the cells
> left-justifed.  If you remove justification, correct times will be
> right-justified, and incorrect ones will be interpreted as text and
> therefore left-justified.  This helps in noticing a problem early on.****

> **4)     **Get rid of the TEXT function in your total formula (the cell
> format takes care of formatting), and simply use:****

> =SUM(B2:B8)****

> ** **

> *For the Total login cell* in the summary area (F3), simply refer to the
> total in B9 directly:****

> =B9****

> ** **

> *For the No of days cell* (F4):****

> **a.      **If you need the number of 24-hour periods for the Total login
> time use:****

> =B9****

> and format the cell as a number (it will default to time format).  The
> result will be in terms of days, since that is how Excel stores dates and
> times.****

> **b.      **If you need the number of periods of length "1 day hour" (the
> number in F1), use:****

> =B9*24/F1****

> (and format the cell as a number)****

> ** **

> *For the Login time for 1 day cell* (F5), I don't really understand the
> formula or what you want, but perhaps the formula is correct and you just
> need to format the result for time:****

> [h]:mm:ss****

> ** **

> Asa****

> ** **



> *Sent:* Saturday, June 02, 2012 12:09 AM


> *Subject:* $$Excel-Macros$$ Date format calculation****

> ** **

> Dear Experts,

>     i have again got confusion in date and time format calculation, Please
> see the attached file, i have elaborate the details in the sheet. Please
> help in calculating the problem.

>   ****

>  ****

> Thanks & Regards,
> *Deba Ranjan P*****

> ** **

> ** **

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


 
 
 

Date format calculation

Post by Asa Rossof » Tue, 05 Jun 2012 17:28:31


Sorry, I don't have time.  I tried to be very specific, but post back with
questions if you can't follow what I said.

Asa


On Behalf Of Deba Ranjan
Sent: Monday, June 04, 2012 1:24 AM

Subject: Re: $$Excel-Macros$$ Date format calculation

Dear Asa,

   Can you please explain in the sheet itself.

Thanks & Regards,
Deba Ranjan P

Hi Deba,

For the Login Time column, the reason SUM is not working properly is that
many of the login times are being interpreted as text instead of as
[h]:mm:ss entries.

1)      For the login time column with a custom format:

[h]:mm:ss

or the corresponding preset "Time" format:

37:30:55

2)      Then, only enter valid times.  Your problem entries all have minutes
or seconds > 59, and Excel knows that this could not indicate time properly,
so treats it as text.  Correct the following entries:

879:55:64

899:54:78

98:54:87

87:54:78

198:89:55

3)      Optionally remove cell justification.  You have the cells
left-justifed.  If you remove justification, correct times will be
right-justified, and incorrect ones will be interpreted as text and
therefore left-justified.  This helps in noticing a problem early on.

4)     Get rid of the TEXT function in your total formula (the cell format
takes care of formatting), and simply use:

=SUM(B2:B8)

For the Total login cell in the summary area (F3), simply refer to the total
in B9 directly:

=B9

For the No of days cell (F4):

a.      If you need the number of 24-hour periods for the Total login time
use:

=B9

and format the cell as a number (it will default to time format).  The
result will be in terms of days, since that is how Excel stores dates and
times.

b.      If you need the number of periods of length "1 day hour" (the number
in F1), use:

=B9*24/F1

(and format the cell as a number)

For the Login time for 1 day. cell (F5), I don't really understand the
formula or what you want, but perhaps the formula is correct and you just
need to format the result for time:

[h]:mm:ss

Asa


On Behalf Of Deba Ranjan
Sent: Saturday, June 02, 2012 12:09 AM


Subject: $$Excel-Macros$$ Date format calculation

Dear Experts,

    i have again got confusion in date and time format calculation, Please
see the attached file, i have elaborate the details in the sheet. Please
help in calculating the problem.

Thanks & Regards,
Deba Ranjan P

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to


--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Date format calculation

Post by Rajan_Verm » Tue, 05 Jun 2012 21:59:55


But how can second be 64?

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of Deba Ranjan
Sent: 04 June 2012 9:27

Subject: Re: $$Excel-Macros$$ Date format calculation

Yes.......exactly.

Thanks & Regards,
Deba Ranjan P



Hi

What do you mean by this Login time : "879:55:64" is it 879 Hour 55 Minute
and 64 Seconds??

Regards

Rajan verma

+91 7838100659 <tel:%2B91%207838100659>  [IM-Gtalk]


On Behalf Of Deba Ranjan
Sent: 02 June 2012 12:39

Subject: $$Excel-Macros$$ Date format calculation

Dear Experts,

    i have again got confusion in date and time format calculation, Please
see the attached file, i have elaborate the details in the sheet. Please
help in calculating the problem.

Thanks & Regards,
Deba Ranjan P

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to


--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Date format calculation

Post by Deba Ranja » Tue, 05 Jun 2012 22:18:13


Sir, i mean the times are like wise:

  hour, minute, second

  It can be 2000 hours
  can be 300 mins
 can be 200 seconds...........

Thanks & Regards,
*Deba Ranjan P*

****


> But how can second be 64?****

> ** **

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*

> ** **



> *Sent:* 04 June 2012 9:27


> *Subject:* Re: $$Excel-Macros$$ Date format calculation****

> ** **

> Yes.......exactly.
> ****

>  ****

> Thanks & Regards,
> *Deba Ranjan P*****

> ** **

> ****



> Hi****

> What do you mean by this Login time : 879:55:64 is it 879 Hour 55
> Minute and 64 Seconds??****

>  ****

> * *****

> *Regards*****

> *Rajan verma*****

> *+91 7838100659 [IM-Gtalk]*****

>  ****



> *Sent:* 02 June 2012 12:39

> *Subject:* $$Excel-Macros$$ Date format calculation****

>  ****

> Dear Experts,

>     i have again got confusion in date and time format calculation, Please
> see the attached file, i have elaborate the details in the sheet. Please
> help in calculating the problem.

>   ****

>  ****

> Thanks & Regards,
> *Deba Ranjan P*****

>  ****

>  ****

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> ** **

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------

> To unsubscribe, send a blank email to


 
 
 

Date format calculation

Post by Rajan_Verm » Tue, 05 Jun 2012 22:52:58


Hi Deba

I think when you will enter valid time .. your formula is giving correct
results

Regards

Rajan verma

+91 7838100659 [IM-Gtalk]


On Behalf Of Deba Ranjan
Sent: 04 June 2012 6:48

Subject: Re: $$Excel-Macros$$ Date format calculation

Sir, i mean the times are like wise:

  hour, minute, second

  It can be 2000 hours
  can be 300 mins
 can be 200 seconds...........

Thanks & Regards,
Deba Ranjan P



But how can second be 64?

Regards

Rajan verma

+91 7838100659 <tel:%2B91%207838100659>  [IM-Gtalk]


On Behalf Of Deba Ranjan
Sent: 04 June 2012 9:27


Subject: Re: $$Excel-Macros$$ Date format calculation

Yes.......exactly.

Thanks & Regards,
Deba Ranjan P



Hi

What do you mean by this Login time : "879:55:64" is it 879 Hour 55 Minute
and 64 Seconds??

Regards

Rajan verma

+91 7838100659 <tel:%2B91%207838100659>  [IM-Gtalk]


On Behalf Of Deba Ranjan
Sent: 02 June 2012 12:39

Subject: $$Excel-Macros$$ Date format calculation

Dear Experts,

    i have again got confusion in date and time format calculation, Please
see the attached file, i have elaborate the details in the sheet. Please
help in calculating the problem.

Thanks & Regards,
Deba Ranjan P

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to


--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to


--
FORUM RULES (986+ members already BANNED for violation)

1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.

2) Don't post a question in the thread of another member.

3) Don't post questions regarding breaking or bypassing any security
measure.

4) Acknowledge the responses you receive, good or bad.

5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.

NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.

----------------------------------------------------------------------------
--------------------------

To unsubscribe, send a blank email to

 
 
 

Date format calculation

Post by David Grugeo » Wed, 06 Jun 2012 10:41:56


You may need a formula like
=LEFT(A1,FIND(":",A1)-1)/24+MID(A1,FIND(":",A1)+1,FIND(":",A1,FIND(":",A1)+1)-(FIND(":",A1)+1))/1440+MID(A1,FIND(":",A1,FIND(":",A1)+1)+1,99)/86400

This will convert your pseudo time values to date/time values.  Then you
can do further processing on them.
On 4 June 2012 23:52, Rajan_Verma <rajanverma1...@gmail.com> wrote:

> Hi Deba****

> ** **

> I think when you will enter valid time .. your formula is giving correct
> results****

> ** **

> * *

> *Regards*

> *Rajan verma*

> *+91 7838100659 [IM-Gtalk]*

> ** **

> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Deba Ranjan
> *Sent:* 04 June 2012 6:48

> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Date format calculation****

> ** **

> Sir, i mean the times are like wise:

>   hour, minute, second

>   It can be 2000 hours
>   can be 300 mins
>  can be 200 seconds...........
> ****

>  ****

> Thanks & Regards,
> *Deba Ranjan P*****

> ** **

> ****

> On Mon, Jun 4, 2012 at 6:29 PM, Rajan_Verma <rajanverma1...@gmail.com>
> wrote:****

> But how can second be 64?****

>  ****

> * *****

> *Regards*****

> *Rajan verma*****

> *+91 7838100659 [IM-Gtalk]*****

>  ****

> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Deba Ranjan
> *Sent:* 04 June 2012 9:27****

> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ Date format calculation****

>  ****

> Yes.......exactly.
> ****

>  ****

> Thanks & Regards,
> *Deba Ranjan P*****

>  ****

> ** **

> On Sun, Jun 3, 2012 at 1:28 PM, Rajan_Verma <rajanverma1...@gmail.com>
> wrote:****

> Hi****

> What do you mean by this Login time : 879:55:64 is it 879 Hour 55
> Minute and 64 Seconds??****

>  ****

> * *****

> *Regards*****

> *Rajan verma*****

> *+91 7838100659 [IM-Gtalk]*****

>  ****

> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Deba Ranjan
> *Sent:* 02 June 2012 12:39
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Date format calculation****

>  ****

> Dear Experts,

>     i have again got confusion in date and time format calculation, Please
> see the attached file, i have elaborate the details in the sheet. Please
> help in calculating the problem.

>   ****

>  ****

> Thanks & Regards,
> *Deba Ranjan P*****

>  ****

>  ****

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com

> To unsubscribe, send a blank email to
> excel-macros+unsubscribe@googlegroups.com****

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com

> To unsubscribe, send a blank email to
> excel-macros+unsubscribe@googlegroups.com****

>  ****

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com

> To unsubscribe, send a blank email to
> excel-macros+unsubscribe@googlegroups.com****

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com

> To unsubscribe, send a blank email to
> excel-macros+unsubscribe@googlegroups.com****

> ** **

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com

> To unsubscribe, send a blank email to
> excel-macros+unsubscribe@googlegroups.com****

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com

> To unsubscribe, send a blank email to
> excel-macros+unsubscribe@googlegroups.com

--
David Grugeon
 
 
 

Date format calculation

Post by Deba Ranja » Wed, 06 Jun 2012 12:48:29


Thanks its working...thank you very much.

Thanks & Regards,
*Deba Ranjan P*

****

On Tue, Jun 5, 2012 at 7:11 AM, David Grugeon <da...@grugeon.com.au> wrote:
> You may need a formula like
> =LEFT(A1,FIND(":",A1)-1)/24+MID(A1,FIND(":",A1)+1,FIND(":",A1,FIND(":",A1)+1)-(FIND(":",A1)+1))/1440+MID(A1,FIND(":",A1,FIND(":",A1)+1)+1,99)/86400

> This will convert your pseudo time values to date/time values.  Then you
> can do further processing on them.

> On 4 June 2012 23:52, Rajan_Verma <rajanverma1...@gmail.com> wrote:

>> Hi Deba****

>> ** **

>> I think when you will enter valid time .. your formula is giving correct
>> results****

>> ** **

>> * *

>> *Regards*

>> *Rajan verma*

>> *+91 7838100659 [IM-Gtalk]*

>> ** **

>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *Deba Ranjan
>> *Sent:* 04 June 2012 6:48

>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Date format calculation****

>> ** **

>> Sir, i mean the times are like wise:

>>   hour, minute, second

>>   It can be 2000 hours
>>   can be 300 mins
>>  can be 200 seconds...........
>> ****

>>  ****

>> Thanks & Regards,
>> *Deba Ranjan P*****

>> ** **

>> ****

>> On Mon, Jun 4, 2012 at 6:29 PM, Rajan_Verma <rajanverma1...@gmail.com>
>> wrote:****

>> But how can second be 64?****

>>  ****

>> * *****

>> *Regards*****

>> *Rajan verma*****

>> *+91 7838100659 [IM-Gtalk]*****

>>  ****

>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *Deba Ranjan
>> *Sent:* 04 June 2012 9:27****

>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ Date format calculation****

>>  ****

>> Yes.......exactly.
>> ****

>>  ****

>> Thanks & Regards,
>> *Deba Ranjan P*****

>>  ****

>> ** **

>> On Sun, Jun 3, 2012 at 1:28 PM, Rajan_Verma <rajanverma1...@gmail.com>
>> wrote:****

>> Hi****

>> What do you mean by this Login time : 879:55:64 is it 879 Hour 55
>> Minute and 64 Seconds??****

>>  ****

>> * *****

>> *Regards*****

>> *Rajan verma*****

>> *+91 7838100659 [IM-Gtalk]*****

>>  ****

>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *Deba Ranjan
>> *Sent:* 02 June 2012 12:39
>> *To:* excel-macros@googlegroups.com
>> *Subject:* $$Excel-Macros$$ Date format calculation****

>>  ****

>> Dear Experts,

>>     i have again got confusion in date and time format calculation,
>> Please see the attached file, i have elaborate the details in the sheet.
>> Please help in calculating the problem.

>>   ****

>>  ****

>> Thanks & Regards,
>> *Deba Ranjan P*****

>>  ****

>>  ****

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com****

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com****

>>  ****

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com****

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com****

>> ** **

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com****

>> --
>> FORUM RULES (986+ members already BANNED for violation)

>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>> will not get quick attention or may not be answered.

>> 2) Don't post a question in the thread of another member.

>> 3) Don't post questions regarding breaking or bypassing any security
>> measure.

>> 4) Acknowledge the responses you receive, good or bad.

>> 5) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.

>> NOTE : Don't ever post personal or confidential data in a workbook. Forum
>> owners and members are not responsible for any loss.

>> ------------------------------------------------------------------------------------------------------
>> To post to this group, send email to excel-macros@googlegroups.com

>> To unsubscribe, send a blank email to
>> excel-macros+unsubscribe@googlegroups.com

> --
> David Grugeon

> --
> FORUM RULES (986+ members already BANNED for violation)

> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.

> 2) Don't post a question in the thread of another member.

> 3) Don't post questions regarding breaking or bypassing any security
> measure.

> 4) Acknowledge the responses you receive, good or bad.

> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.

> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.

...

read more »

 
 
 

1. PB DATE format vs WIN.INI DATE format

Hello,

I've a question about PB date edit style.

eg. edit style of dw's column X is specified as (dd.mm.yy)

However, it results that the edit style is mm.dd.yy if
the win.ini section [intl].date = mm/dd/yy.

if win.ini section [intl].date = dd/mm/yy, the specified
(dd.mm.yy) edit style of PB's dw  is OK.

Can I develop a routine which can modify the windows's
date setting to be dd.mm.yy and how?

Thanks for your attention
Barry

2. Information please

3. Date Format...Date to "Quarter"

4. Linux Client

5. Coverting raw date format image file to GIF format

6. WinNuke for Windows 95 & NT

7. How to Format date as locale format

8. Problems with buttons

9. REPOST: Time Calculations -- not specific to date or hour of day

10. Time Calculations -- not specific to date or hour of day

11. Date Calculation

12. Future date calculations

13. Date Calculations