|
List Info
Thread: Time Difference Display format
|
|
| Time Difference Display format |
|
2006-06-29 16:40:44 |
Wyatt,
Don Guillett gave me a TEXT solution early on that works
fine. I was
hoping for a format option that would preserve the numbers
that I could
then sum them.
Paul J Koch
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G PEACH.EASE.LSOFT.COM]
On Behalf Of Lemmons, Wyatt
Sent: Thursday, June 29, 2006 8:37 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Been watching and this has been buggin' me ... So started
goofing and
given yer example values below in A1 and A2:
=TEXT(INT((A1-A2)),"0")&" days
"&TEXT(MOD(A1-A2,1),"h:m:s")
Returns:
107 days 12:15:34
When A1 is less than A2, you'd get: -108 days 11:44:26 ...
SO, try:
=TEXT(IF(A1-A2<0,ABS(ROUNDDOWN(A1-A2,0)),INT(A1-A2)),&qu
ot;0")&" days
"&TEXT(IF(A1-A2<0,1-MOD(A1-A2,1),MOD(A1-A2,1)),
"h:m:s")
It ain't display, and ya can't do math on the results, but
it ends up
showing the desired image of the elapsed time ...
HTH
thank you,
Wyatt Lemmons, HSG
(425) 830-5962 4x10 Wrk Sch ( Fri OFF ) & Telecommute:
Wed
-----Original Message-----
From: Stuart Dunlap [mailto:sdunlapBROWNSHOE.COM]
Sent: Thursday, June 29, 2006 7:30 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Paul,
Something along the line of this may do it: y
"years" m "months" d
"days
" h:mm:ss
Stu
=====================================================
"Koch, Paul (PJKO)" <PJKOCHEVRON.COM>
Sent by: MS Excel General Q & A List <EXCEL-GPEACH.EASE.LSOFT.COM>
06/29/2006 08:56 AM
Please respond to
MS Excel General Q & A List <EXCEL-GPEACH.EASE.LSOFT.COM>
To
EXCEL-GPEACH.EASE.LSOFT.COM
cc
Subject
Re: Time Difference Display format
Thanks Robert. That works unless my elapsed time is greater
than 32
days. My example displays as 16 days 12:15:34 because the
difference is
calculated as 4/16/00 12:15:34.
Paul J Koch
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Robert McCurdy
Sent: Thursday, June 29, 2006 1:29 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Try this custom number format.
d "days " h:mm:ss
Regards
Robert McCurdy
----- Original Message -----
From: "Koch, Paul (PJKO)" <PJKOCHEVRON.COM>
To: <EXCEL-GPEACH.EASE.LSOFT.COM>
Sent: Thursday, June 29, 2006 6:09 AM
Subject: Time Difference Display format
Is there a way to subtract two time values and have the
result displayed
as d h:mm:ss where the hours will be less than 24 and the d
represents
the number of days in the difference.
Example: 1/9/05 2:57:08 - 9/23/04 14:41:34 would display as
107 12:15:34
instead of 1280:15:34
Paul J Koch
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|
|
| Time Difference Display format |
|
2006-06-29 17:00:47 |
A couple of suggestions:
1) You could just use minutes [h]:mm:ss
2) Or if you use 2 columns, =INT(A1-A2) formatted as 0
"days" and in the
2nd one =A1-A2 formatted as "hh:mm:ss". At least
you could sum these.
Cheers
Pete
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Koch, Paul (PJKO)
Sent: 29 June 2006 17:41
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Wyatt,
Don Guillett gave me a TEXT solution early on that works
fine. I was
hoping for a format option that would preserve the numbers
that I could
then sum them.
Paul J Koch
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Lemmons, Wyatt
Sent: Thursday, June 29, 2006 8:37 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Been watching and this has been buggin' me ... So started
goofing and
given yer example values below in A1 and A2:
=TEXT(INT((A1-A2)),"0")&" days
"&TEXT(MOD(A1-A2,1),"h:m:s")
Returns:
107 days 12:15:34
When A1 is less than A2, you'd get: -108 days 11:44:26 ...
SO, try:
=TEXT(IF(A1-A2<0,ABS(ROUNDDOWN(A1-A2,0)),INT(A1-A2)),&qu
ot;0")&" days
"&TEXT(IF(A1-A2<0,1-MOD(A1-A2,1),MOD(A1-A2,1)),
"h:m:s")
It ain't display, and ya can't do math on the results, but
it ends up
showing the desired image of the elapsed time ...
HTH
thank you,
Wyatt Lemmons, HSG
(425) 830-5962 4x10 Wrk Sch ( Fri OFF ) & Telecommute:
Wed
-----Original Message-----
From: Stuart Dunlap [mailto:sdunlapBROWNSHOE.COM]
Sent: Thursday, June 29, 2006 7:30 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Paul,
Something along the line of this may do it: y
"years" m "months" d
"days
" h:mm:ss
Stu
=====================================================
"Koch, Paul (PJKO)" <PJKOCHEVRON.COM>
Sent by: MS Excel General Q & A List <EXCEL-GPEACH.EASE.LSOFT.COM>
06/29/2006 08:56 AM
Please respond to
MS Excel General Q & A List <EXCEL-GPEACH.EASE.LSOFT.COM>
To
EXCEL-GPEACH.EASE.LSOFT.COM
cc
Subject
Re: Time Difference Display format
Thanks Robert. That works unless my elapsed time is greater
than 32
days. My example displays as 16 days 12:15:34 because the
difference is
calculated as 4/16/00 12:15:34.
Paul J Koch
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Robert McCurdy
Sent: Thursday, June 29, 2006 1:29 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Try this custom number format.
d "days " h:mm:ss
Regards
Robert McCurdy
----- Original Message -----
From: "Koch, Paul (PJKO)" <PJKOCHEVRON.COM>
To: <EXCEL-GPEACH.EASE.LSOFT.COM>
Sent: Thursday, June 29, 2006 6:09 AM
Subject: Time Difference Display format
Is there a way to subtract two time values and have the
result displayed
as d h:mm:ss where the hours will be less than 24 and the d
represents
the number of days in the difference.
Example: 1/9/05 2:57:08 - 9/23/04 14:41:34 would display as
107 12:15:34
instead of 1280:15:34
Paul J Koch
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
************************************************************
**********
As an environmentally conscious company, Veolia
Environmental Services is keen to help save trees by
reducing paper usage. Please only print out this email if
absolutely necessary. In 2004 the UK used about 13 million
tonnes of paper of which only 36% was recycled.
************************************************************
**********
Veolia Environmental Services Plc Legal Disclaimer
The information in this email and any associated files is
confidential and may be legally privileged. It may also
contain information that is subject to copyright or
constitutes a trade secret. It is intended solely for the
named recipient. Access to this email by anyone else is
unauthorised.
If you are not the intended recipient, please note that any
use, disclosure, copying, distribution of this email or any
action taken or omitted to be taken in reliance on it is
prohibited.
If you are not the intended recipient, please inform us by
telephoning +44 (0) 20 7812 5000, or by fax to +44 (0) 20
7812 5001 and then delete the email and any copies of it.
Our registered office is at Veolia House, 154A Pentonville
Road, bond, N1 9PE.
************************************************************
**********
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|
|
| Time Difference Display format |
|
2006-06-29 17:05:32 |
OH ...
thank you,
Wyatt Lemmons, HSG
(425) 830-5962 4x10 Wrk Sch ( Fri OFF ) & Telecommute:
Wed
-----Original Message-----
From: Koch, Paul (PJKO) [mailto:PJKOCHEVRON.COM]
Sent: Thursday, June 29, 2006 9:41 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Wyatt,
Don Guillett gave me a TEXT solution early on that works
fine. I was
hoping for a format option that would preserve the numbers
that I could
then sum them.
Paul J Koch
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Lemmons, Wyatt
Sent: Thursday, June 29, 2006 8:37 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Been watching and this has been buggin' me ... So started
goofing and
given yer example values below in A1 and A2:
=TEXT(INT((A1-A2)),"0")&" days
"&TEXT(MOD(A1-A2,1),"h:m:s")
Returns:
107 days 12:15:34
When A1 is less than A2, you'd get: -108 days 11:44:26 ...
SO, try:
=TEXT(IF(A1-A2<0,ABS(ROUNDDOWN(A1-A2,0)),INT(A1-A2)),&qu
ot;0")&" days
"&TEXT(IF(A1-A2<0,1-MOD(A1-A2,1),MOD(A1-A2,1)),
"h:m:s")
It ain't display, and ya can't do math on the results, but
it ends up
showing the desired image of the elapsed time ...
HTH
thank you,
Wyatt Lemmons, HSG
(425) 830-5962 4x10 Wrk Sch ( Fri OFF ) & Telecommute:
Wed
-----Original Message-----
From: Stuart Dunlap [mailto:sdunlapBROWNSHOE.COM]
Sent: Thursday, June 29, 2006 7:30 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Paul,
Something along the line of this may do it: y
"years" m "months" d
"days
" h:mm:ss
Stu
=====================================================
"Koch, Paul (PJKO)" <PJKOCHEVRON.COM>
Sent by: MS Excel General Q & A List <EXCEL-GPEACH.EASE.LSOFT.COM>
06/29/2006 08:56 AM
Please respond to
MS Excel General Q & A List <EXCEL-GPEACH.EASE.LSOFT.COM>
To
EXCEL-GPEACH.EASE.LSOFT.COM
cc
Subject
Re: Time Difference Display format
Thanks Robert. That works unless my elapsed time is greater
than 32
days. My example displays as 16 days 12:15:34 because the
difference is
calculated as 4/16/00 12:15:34.
Paul J Koch
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM]
On Behalf Of Robert McCurdy
Sent: Thursday, June 29, 2006 1:29 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Time Difference Display format
Try this custom number format.
d "days " h:mm:ss
Regards
Robert McCurdy
----- Original Message -----
From: "Koch, Paul (PJKO)" <PJKOCHEVRON.COM>
To: <EXCEL-GPEACH.EASE.LSOFT.COM>
Sent: Thursday, June 29, 2006 6:09 AM
Subject: Time Difference Display format
Is there a way to subtract two time values and have the
result displayed
as d h:mm:ss where the hours will be less than 24 and the d
represents
the number of days in the difference.
Example: 1/9/05 2:57:08 - 9/23/04 14:41:34 would display as
107 12:15:34
instead of 1280:15:34
Paul J Koch
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info and
archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|
|
[1-3]
|
|