I have a long spread sheet that has two columns of dates (Received and
Finished) that I am importing into a pivot table and would like to use the
dates in my pivot, less the time. For daily use, I do use the time with the
date, but only
want the date for use in the pivot table. How can I eliminate the time
serial number, before importing into my pivot, apart from editing each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)

You could use helper columns with formulae of

=INT(A1)

etc. and pivot the helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Gotti" wrote in message
...
I have a long spread sheet that has two columns of dates (Received and
Finished) that I am importing into a pivot table and would like to use the
dates in my pivot, less the time. For daily use, I do use the time with
the
date, but only
want the date for use in the pivot table. How can I eliminate the time
serial number, before importing into my pivot, apart from editing each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)

Hi,
I need to only show time as time... for example someone worked on a specific
project for a certain length of time. I've created a pivot table in excel.
On the data tab I have some numbers which are formatted as hours:min:sec (for
example - 3:45:20 = 3 hours, 45 mins, 20 sec.). How do I get that stat to go
over to the pivot table correctly? When I update to the pivot table I only
get 0s. I've tried different formats and nothing seems to work.

I'd appreciate any help in fixing this... thanks,
~kristina

"Bob Phillips" wrote:

You could use helper columns with formulae of

=INT(A1)

etc. and pivot the helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Gotti" wrote in message
...
I have a long spread sheet that has two columns of dates (Received and
Finished) that I am importing into a pivot table and would like to use the
dates in my pivot, less the time. For daily use, I do use the time with
the
date, but only
want the date for use in the pivot table. How can I eliminate the time
serial number, before importing into my pivot, apart from editing each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)

Hi Kristina

On the PT, double click on the relevant field headerNumberCustom [h]:mm

--
Regards
Roger Govier



"Kristina Demers" <Kristina wrote in
message ...
Hi,
I need to only show time as time... for example someone worked on a
specific
project for a certain length of time. I've created a pivot table in
excel.
On the data tab I have some numbers which are formatted as hours:min:sec
(for
example - 3:45:20 = 3 hours, 45 mins, 20 sec.). How do I get that stat to
go
over to the pivot table correctly? When I update to the pivot table I
only
get 0s. I've tried different formats and nothing seems to work.

I'd appreciate any help in fixing this... thanks,
~kristina

"Bob Phillips" wrote:

You could use helper columns with formulae of

=INT(A1)

etc. and pivot the helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Gotti" wrote in message
...
I have a long spread sheet that has two columns of dates (Received and
Finished) that I am importing into a pivot table and would like to use
the
dates in my pivot, less the time. For daily use, I do use the time
with
the
date, but only
want the date for use in the pivot table. How can I eliminate the time
serial number, before importing into my pivot, apart from editing each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)

Thanks! I tried your suggestion. The format comes out as h:mm but it shows
as 0:00.

In case it's relevant, in the data tab I use this formula for the cell:
=TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[h]:mm:ss"))

It returns the correct result on the data tab but this result is not pulling
through to the pivot tab.


"Roger Govier" wrote:

Hi Kristina

On the PT, double click on the relevant field headerNumberCustom [h]:mm

--
Regards
Roger Govier



"Kristina Demers" <Kristina wrote in
message ...
Hi,
I need to only show time as time... for example someone worked on a
specific
project for a certain length of time. I've created a pivot table in
excel.
On the data tab I have some numbers which are formatted as hours:min:sec
(for
example - 3:45:20 = 3 hours, 45 mins, 20 sec.). How do I get that stat to
go
over to the pivot table correctly? When I update to the pivot table I
only
get 0s. I've tried different formats and nothing seems to work.

I'd appreciate any help in fixing this... thanks,
~kristina

"Bob Phillips" wrote:

You could use helper columns with formulae of

=INT(A1)

etc. and pivot the helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Gotti" wrote in message
...
I have a long spread sheet that has two columns of dates (Received and
Finished) that I am importing into a pivot table and would like to use
the
dates in my pivot, less the time. For daily use, I do use the time
with
the
date, but only
want the date for use in the pivot table. How can I eliminate the time
serial number, before importing into my pivot, apart from editing each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)

Hi Kristina

Well that's because the values on your source data are Text not Numeric, so
the PT correctly sums them as 0.
Change your formula to
=--TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[he]:mm:ss"))

The double unary minus will coerce the text value to numeric.
Then do a refresh on the PT, and all should be well.
--
Regards
Roger Govier



"Kristina Demers" wrote in
message ...
Thanks! I tried your suggestion. The format comes out as h:mm but it
shows
as 0:00.

In case it's relevant, in the data tab I use this formula for the cell:
=TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[h]:mm:ss"))

It returns the correct result on the data tab but this result is not
pulling
through to the pivot tab.


"Roger Govier" wrote:

Hi Kristina

On the PT, double click on the relevant field headerNumberCustom
[h]:mm

--
Regards
Roger Govier



"Kristina Demers" <Kristina wrote in
message ...
Hi,
I need to only show time as time... for example someone worked on a
specific
project for a certain length of time. I've created a pivot table in
excel.
On the data tab I have some numbers which are formatted as
hours:min:sec
(for
example - 3:45:20 = 3 hours, 45 mins, 20 sec.). How do I get that stat
to
go
over to the pivot table correctly? When I update to the pivot table I
only
get 0s. I've tried different formats and nothing seems to work.

I'd appreciate any help in fixing this... thanks,
~kristina

"Bob Phillips" wrote:

You could use helper columns with formulae of

=INT(A1)

etc. and pivot the helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Gotti" wrote in message
...
I have a long spread sheet that has two columns of dates (Received
and
Finished) that I am importing into a pivot table and would like to
use
the
dates in my pivot, less the time. For daily use, I do use the time
with
the
date, but only
want the date for use in the pivot table. How can I eliminate the
time
serial number, before importing into my pivot, apart from editing
each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)

It worked - It worked!!! Thank you so much!!!

:)

"Roger Govier" wrote:

Hi Kristina

Well that's because the values on your source data are Text not Numeric, so
the PT correctly sums them as 0.
Change your formula to
=--TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[he]:mm:ss"))

The double unary minus will coerce the text value to numeric.
Then do a refresh on the PT, and all should be well.
--
Regards
Roger Govier



"Kristina Demers" wrote in
message ...
Thanks! I tried your suggestion. The format comes out as h:mm but it
shows
as 0:00.

In case it's relevant, in the data tab I use this formula for the cell:
=TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[h]:mm:ss"))

It returns the correct result on the data tab but this result is not
pulling
through to the pivot tab.


"Roger Govier" wrote:

Hi Kristina

On the PT, double click on the relevant field headerNumberCustom
[h]:mm

--
Regards
Roger Govier



"Kristina Demers" <Kristina wrote in
message ...
Hi,
I need to only show time as time... for example someone worked on a
specific
project for a certain length of time. I've created a pivot table in
excel.
On the data tab I have some numbers which are formatted as
hours:min:sec
(for
example - 3:45:20 = 3 hours, 45 mins, 20 sec.). How do I get that stat
to
go
over to the pivot table correctly? When I update to the pivot table I
only
get 0s. I've tried different formats and nothing seems to work.

I'd appreciate any help in fixing this... thanks,
~kristina

"Bob Phillips" wrote:

You could use helper columns with formulae of

=INT(A1)

etc. and pivot the helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Gotti" wrote in message
...
I have a long spread sheet that has two columns of dates (Received
and
Finished) that I am importing into a pivot table and would like to
use
the
dates in my pivot, less the time. For daily use, I do use the time
with
the
date, but only
want the date for use in the pivot table. How can I eliminate the
time
serial number, before importing into my pivot, apart from editing
each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)

Ok - so it semi worked... but some of them are coming through with #VALUE!

I checked on line and found that this can mean an item was a text.... I made
sure to change all to numbers... but did not fix the error. Do you have any
idea why this may not work for only a few of them? (I download all stats in
the same way...)

"Roger Govier" wrote:

Hi Kristina

Well that's because the values on your source data are Text not Numeric, so
the PT correctly sums them as 0.
Change your formula to
=--TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[he]:mm:ss"))

The double unary minus will coerce the text value to numeric.
Then do a refresh on the PT, and all should be well.
--
Regards
Roger Govier



"Kristina Demers" wrote in
message ...
Thanks! I tried your suggestion. The format comes out as h:mm but it
shows
as 0:00.

In case it's relevant, in the data tab I use this formula for the cell:
=TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[h]:mm:ss"))

It returns the correct result on the data tab but this result is not
pulling
through to the pivot tab.


"Roger Govier" wrote:

Hi Kristina

On the PT, double click on the relevant field headerNumberCustom
[h]:mm

--
Regards
Roger Govier



"Kristina Demers" <Kristina wrote in
message ...
Hi,
I need to only show time as time... for example someone worked on a
specific
project for a certain length of time. I've created a pivot table in
excel.
On the data tab I have some numbers which are formatted as
hours:min:sec
(for
example - 3:45:20 = 3 hours, 45 mins, 20 sec.). How do I get that stat
to
go
over to the pivot table correctly? When I update to the pivot table I
only
get 0s. I've tried different formats and nothing seems to work.

I'd appreciate any help in fixing this... thanks,
~kristina

"Bob Phillips" wrote:

You could use helper columns with formulae of

=INT(A1)

etc. and pivot the helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Gotti" wrote in message
...
I have a long spread sheet that has two columns of dates (Received
and
Finished) that I am importing into a pivot table and would like to
use
the
dates in my pivot, less the time. For daily use, I do use the time
with
the
date, but only
want the date for use in the pivot table. How can I eliminate the
time
serial number, before importing into my pivot, apart from editing
each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)

I think it may be that the number is too small... I'm noticing that sometimes
the linked cell is only = to 5 etc... how do I fix since it's not 0?

"Kristina Demers" wrote:

Ok - so it semi worked... but some of them are coming through with #VALUE!

I checked on line and found that this can mean an item was a text.... I made
sure to change all to numbers... but did not fix the error. Do you have any
idea why this may not work for only a few of them? (I download all stats in
the same way...)

"Roger Govier" wrote:

Hi Kristina

Well that's because the values on your source data are Text not Numeric, so
the PT correctly sums them as 0.
Change your formula to
=--TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[he]:mm:ss"))

The double unary minus will coerce the text value to numeric.
Then do a refresh on the PT, and all should be well.
--
Regards
Roger Govier



"Kristina Demers" wrote in
message ...
Thanks! I tried your suggestion. The format comes out as h:mm but it
shows
as 0:00.

In case it's relevant, in the data tab I use this formula for the cell:
=TEXT(Q7002/86400,CHOOSE(MATCH(Q7002,{0,60,3600},1),":ss","m:s s","[h]:mm:ss"))

It returns the correct result on the data tab but this result is not
pulling
through to the pivot tab.


"Roger Govier" wrote:

Hi Kristina

On the PT, double click on the relevant field headerNumberCustom
[h]:mm

--
Regards
Roger Govier



"Kristina Demers" <Kristina wrote in
message ...
Hi,
I need to only show time as time... for example someone worked on a
specific
project for a certain length of time. I've created a pivot table in
excel.
On the data tab I have some numbers which are formatted as
hours:min:sec
(for
example - 3:45:20 = 3 hours, 45 mins, 20 sec.). How do I get that stat
to
go
over to the pivot table correctly? When I update to the pivot table I
only
get 0s. I've tried different formats and nothing seems to work.

I'd appreciate any help in fixing this... thanks,
~kristina

"Bob Phillips" wrote:

You could use helper columns with formulae of

=INT(A1)

etc. and pivot the helper columns.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Bob Gotti" wrote in message
...
I have a long spread sheet that has two columns of dates (Received
and
Finished) that I am importing into a pivot table and would like to
use
the
dates in my pivot, less the time. For daily use, I do use the time
with
the
date, but only
want the date for use in the pivot table. How can I eliminate the
time
serial number, before importing into my pivot, apart from editing
each
individual cell?

Have a \\\|/// Blessed Day
(o o)
---oOO-(_)-OOo---

But as for me, I watch in hope
for the LORD, I wait for God my
Savior; My God will hear me.
(Micah 7:7)