i have excel spreadsheet 3 columns , few hundred rows. 1 of columns "date," date in following format: m/d/yyyy h:mm pm est, believe excel sees text, not actual dates.
unfortunately, data in spreadsheet output of analytics software doesn't allow me customize output. because can't figure out how either reformat date yyyy/mm/dd or excel recognize dates , sort them accordingly without reformatting, trying sort date not sort date.
here's example of few rows' worth of dates incorrectly sorted excel:
code:
1/29/2011 2:07 pm est 1/31/2009 5:21 pm est 1/6/2012 6:49 pm est 1/6/2012 8:34 est 10/11/2008 6:34 edt
you'll notice years out of order, , after january (month "1") ends, october (month "10") follows instead of february. it's not accurately sorting month. being said, want complete date sorting, in full chronological order.
appreciated. thank you!
those aren't valid date formats excel. them proper format, you'll have use "text columns" separate date time , am/pm , time zone. you'll have format each column appropriately.
might able parse them using datevalue formula.
http://www.yogeshguptaonline.com/2009/10/excel-convert-text-to-date.html
convert dates stored text dates - excel - office.com
Forums Macs Mac Apps and Mac App Store
- iPhone
- Mac OS & System Software
- iPad
- Apple Watch
- Notebooks
- iTunes
- Apple ID
- iCloud
- Desktop Computers
- Apple Music
- Professional Applications
- iPod
- iWork
- Apple TV
- iLife
- Wireless
No comments:
Post a Comment