Need formula to convert a timestamp to date

Anthony_2905

New Member
Joined
Jan 18, 2014
Messages
2
Okay, so the data I'm looking at exports in this format "10/1/2013 9:25:00 AM", which I need to convert to "10/1/13", or something similar, stripping out the time element. The obvious answer, format cells/date is not helpful, as I need to manipulate the exported data in a pivot table, which would just revert the formatted value back to a timestamp.

My question: does anyone know a formula that will change 10/1/2013 9:25:00 AM to 10/1/13, so that when I look to display three months of data, I have around 90 date values, and not 3,000 or so? I Google'd this once before and found it, but I can't find it again. I do not work with any macro's, backend coding, etc., so please someone post a formula for this and I will be much appreciative!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
if you insert a column then take the int of the timestamp...

=int(a1) then drag it down and format it as a short date
 
Upvote 0
Hey thanks a lot for taking the time to answer this post - it worked. When I used that formula, it changed the datetimestamp from "1/9/2007 3:59" to "1/9/2007 0:00". Then I formatted cells "1/9/2007 0:00" to date, which removed the zeros. When I ran the pivot table it did not have the timestamp.

Thanks again, I spent 40 minutes scouring the internet, getting all this unnecessary UNIX-related information, etc. and this took 5 seconds. Glad I joined this website today.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top