Question on Date & Time handling

[David Lipetz]

In a spreadsheet with raw data, there is a column that contains data that
Excel shows as a date and time (10/6/2007 11:08:00 AM). While I can format
the cells to only show the date, the raw data still contains the time
element. Since I want to create a Pivot Table by date, I need to strip out
the time element so that I can perform analysis by date - the time is
irrelevant.

Formatting the cell as a number reveals: 39361.4638888889

What can I do top remove the time element so only the date remains?

Thanks,
David

 

[Bob Phillips]

In an adjacent column, add a formula of

=MOD(A1,1)

format that as date and pivot on the new column.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Roger Govier]

Hi Bob

I think you meant to say
=INT(A1)
to get the Date

 

[David Lipetz]

Thanks guys. I eventually got it to work by using =TRUNC(A1)

Now how do I evaluate those cells so that the pivot table just considers the
month and year? I'm looking for total number of records in a given
month/year, regardless of day.

Thanks,
David

 

[Roger Govier]

Hi David

Thanks guys. I eventually got it to work by using =TRUNC(A1)

That saved formatting the cell as dd/mm/yyyy as well. Well done.

Click on your Date field and from the dropdown on the PT toolbar, select
Group and Outline>Group>Select Month AND Year.

Data will now be aggregated by Month inside Year.
Note, the Date filed has to be a Row item (preferably) or Column item
for Grouping to work.
Also, Grouping fails if any values within the range are Blank or
non-Excel dates.

Once Grouped, you can drag Year and or Month independently to the Page
area if required.

 

[Bob Phillips]

You can right click on the date column (in the pivot Table) and choose
group, then group on year and month.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

I did, thanks Roger.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dave Peterson]

And if the OP is grouping by days (and months), then the OP could just leave the
time in the cell with the date????

 

[David Lipetz]

Excellent. Thank you - this newsgroup is THE most supportive and responsive
as any group I have ever participated in. Thanks again!

 

[Roger Govier]

Very true, Dave.

--
Regards

Roger Govier

And if the OP is grouping by days (and months), then the OP could just
leave the
time in the cell with the date????

 

[david.decastro]

Hi I have a similar but different query.

I have a file which includes a timestamp of the format yyyy mm dd hh mm
(mins) ss 00 an example of which being 2006032211272800 being March
22nd 2006 at 11:27:28 am. Note the last 2 digits are always 00 which
can be removed by dividing by 100

The clock is 24 hr and both minutes and seconds roll over at 60 as
opposed to being decimal.

What I am trying to ascertain is how I can get excel to recognize this
number as being in this format and thereby determine time lapsed
between records

examples of entries are:

Cell b1: 2006032211272800
Cell b2: 2006032211273100
Cell b3: 2006032211282100
Cell b4: 2006032211283800
Cell b5: 2006032211285000
Cell b6: 2006032211290600

Any ideas

Dave

 

[david.decastro]

Hi I have a similar but different query.

I have a file which includes a timestamp of the format yyyy mm dd hh mm
(mins) ss 00 an example of which being 2006032211272800 being March
22nd 2006 at 11:27:28 am. Note the last 2 digits are always 00 which
can be removed by dividing by 100

The clock is 24 hr and both minutes and seconds roll over at 60 as
opposed to being decimal.

What I am trying to ascertain is how I can get excel to recognize this
number as being in this format and thereby determine time lapsed
between records

examples of entries are:

Cell b1: 2006032211272800
Cell b2: 2006032211273100
Cell b3: 2006032211282100
Cell b4: 2006032211283800
Cell b5: 2006032211285000
Cell b6: 2006032211290600

Any ideas

Dave

 

[david.decastro]

Hi I have a similar but different query.

I have a file which includes a timestamp of the format yyyy mm dd hh mm
(mins) ss 00 an example of which being 2006032211272800 being March
22nd 2006 at 11:27:28 am. Note the last 2 digits are always 00 which
can be removed by dividing by 100

The clock is 24 hr and both minutes and seconds roll over at 60 as
opposed to being decimal.

What I am trying to ascertain is how I can get excel to recognize this
number as being in this format and thereby determine time lapsed
between records

examples of entries are:

Cell b1: 2006032211272800
Cell b2: 2006032211273100
Cell b3: 2006032211282100
Cell b4: 2006032211283800
Cell b5: 2006032211285000
Cell b6: 2006032211290600

Any ideas

Dave

 

[Bob Phillips]

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dave Peterson]

You have replies at your other post.

Hi I have a similar but different query.

I have a file which includes a timestamp of the format yyyy mm dd hh mm
(mins) ss 00 an example of which being 2006032211272800 being March
22nd 2006 at 11:27:28 am. Note the last 2 digits are always 00 which
can be removed by dividing by 100

The clock is 24 hr and both minutes and seconds roll over at 60 as
opposed to being decimal.

What I am trying to ascertain is how I can get excel to recognize this
number as being in this format and thereby determine time lapsed
between records

examples of entries are:

Cell b1: 2006032211272800
Cell b2: 2006032211273100
Cell b3: 2006032211282100
Cell b4: 2006032211283800
Cell b5: 2006032211285000
Cell b6: 2006032211290600

Any ideas

Dave