Changing dd/mm/yy tt:ss to simple dd/mm/yy

[Louise99]

Help! I have a spreadsheet where I am applying a pivot table to find average costs per day. The problem is that my raw data shows the dates as 10/01/03 12:22:00 PM. Of course, when I create the table I get sometimes hundreds of lines for each day because it is separating the days by time of day. I just want to get rid of the time so I can run the pivot table to calcuate by each single date only.

I know that there are many of you out there who know exactly how to do this. Please share your knowledge with me - I'd really appreciate it!!

Thanks in advance,
krfb ;-)

Thanks!kb

 

[dcompto]

Format > Cells
In the left window, select Date
In the right window, select the desired date format
OK

 

[Louise99]

Sorry, that didn't do the trick. Thanks anyway.

Thanks!kb

 

[JeanetteM]

This actually should do the trick. Make sure you have chosen the cell you want to change before you do the above technique.

For all your virtual administrative needs visit

http://www3.sympatico.ca/lanceandjeanette

 

[Chris1980]

change the registry... User - Control Panel - International
the setting with tt:ss should be obvious. Just remove them from it.
Of course this may be a bit extreme but it will do the trick.

 

[01Patrik]

Time is calculated as parts of days. This means that today at noon will be (example) 38261,5

This means that you can not vlookup and other stuff that requires exact matches. In order to avoit this problem, use another column and

=ROUNDDOWN(a1,0) where A1 is youre date.

// Patrik

 

[xlbo]

or just
=INT(A1)

1 day = 1 therefore, the integer of any date/time value is teh date

If changing the format on its own does not work, then I would suggest that you are not working with true dates and that they have been imported from another system. If this is the case, you will 1st need to convert your "text that looks like dates" to actual dates.

To achieve this, the easiest way is the "Times 1 fix"

Enter a 1 into any blank cell
Copy it
Select all the cells you want to convert

Edit>PasteSpecial - choose values and tick multiply

et voila - you now have proper dates

NOW when you format, it should sort you out


Other than that, you can use the INT function on the date or roght click on the date field in the pivot table and choose "Group By" - you should then be able to choose "Days"

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

krfb,

In response to a suggesion to change the Cell Number Format

Sorry, that didn't do the trick.

This tell's me that krfb has a STRING and NOT a REAL DATE/TIME VALUE.

Since you have TEXT and not a date, do this.

If your string is in A1...
[tt]
=DATE("20"&MID(A1,FIND(" ",A1)-2,2),LEFT(A1,2),MID(A1,4,2))
[/tt]





Skip,
[sub]
[red]Be advised:[/red] It's been reported that a wee psychic is roaming the countryside.
Small Medium @ Large! [/sub]

 

[pbrodsky]

in the pivot table pop-up menu (right click) select "Group and Show Detail" -> "Group". That should give you options for grouping different time period.

 

[rss01]

Here's another option.

This is in A1

10/01/03 12:22:00 PM

In B1 put

=VALUE(LEFT(A1,8)) and change the cell format to mm/dd/yyyy

Randy