time serial number

[Guest]

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)

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Roger Govier]

Hi Kristina

On the PT, double click on the relevant field header>Number>Custom> [h]:mm

 

[Guest]

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:ss","[h]:mm:ss"))

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

Hi Kristina

On the PT, double click on the relevant field header>Number>Custom> [h]:mm

--
Regards
Roger Govier

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

 

[Roger Govier]

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:ss","[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

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:ss","[h]:mm:ss"))

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

Hi Kristina

On the PT, double click on the relevant field header>Number>Custom>
[h]:mm

--
Regards
Roger Govier

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

:

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)

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)

 

[Guest]

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

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:ss","[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

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:ss","[h]:mm:ss"))

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

Hi Kristina

On the PT, double click on the relevant field header>Number>Custom>
[h]:mm

--
Regards
Roger Govier



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

:

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)

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)

 

[Guest]

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...)

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:ss","[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

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:ss","[h]:mm:ss"))

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

Hi Kristina

On the PT, double click on the relevant field header>Number>Custom>
[h]:mm

--
Regards
Roger Govier



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

:

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)

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)

 

[Guest]

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?

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...)

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:ss","[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

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:ss","[h]:mm:ss"))

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


:

Hi Kristina

On the PT, double click on the relevant field header>Number>Custom>
[h]:mm

--
Regards
Roger Govier



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

:

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)

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)