Hi,
I have brought over some external delimited data to an excel sheet.
There is currently one coumn of dates: yyyy/mm/dd
The column next to it is supposed to be a time field ie. 9:45, 10:35, 14:35 but right now it just shows as 945 or 1035 or 1435.
Question 1:
Is there a way to add the ":" to make it a time field
Question 2:
Is there a way to add in another column that combines the two columns into one column
ie. yyyy/mm/dd hh:mm
Hello again....are these real excel dates?....do you want the times to be real excel times?....this is important because if you are going to be doing date/time math, it is easier if they are real excel date/times....thanks
Ernest
Please consider adding a * if I helped
Nothing drives me crazy - I'm always close enough to walk....
Hi,
I would "assume" that I want them to be real excel times? I am not sure what you mean by that. As of right now though, I will not be doing any "math", I would be exporting the data to Access to merge with other tables.
Currently though, they are formatted as "general"..I tried to format them as a Custom and adding the : and also tried to format them as "time" but that obviously did not work!
can you you upload a bit of it so I can see the current formatting?...thanks
Hi,
I have attached a sample.
So Column G is the date: yyyy/mm/dd
Column H is the time, it is currently in 24 hour clock to differeniate between am and pm which is fine, but it is missing the :
And then I would insert another column after that to combine columns G and H
what I mean by "Real" excel date/time....excel stores date/time in a number. Starting from 1/1/1900 and counts the days. So, today is 41439. The time is the decimal portion. So, .5 is noon, .75 is 6PM. The formatting, as with all formatting, doesn't change the value, only the way it's presented to the viewer....
Originally Posted by judgeh59
O, interesting. Then no as you can see in the previous post that they are not real excel times. I would think that they need to be "true excel times" in order to export to Access
Last edited by marshymell0; 06-14-2013 at 10:36 AM.
so, take a look at this....
the time column is just formating....look at the custom formatting
then look at the really gross formula that just adds the two cells together once I used the TIMEVALUE function...
Why does this time value function change the first part of the date from yyyy/mm/dd to m/dd/yy???
It doesn't that a formatting issue....you can use a custom format like this
yyyy-mm-dd hh:mm
Wow, this is amazing it works perfectly! Thanks so much!
My Pleasure....glad I could help....
Reminder to set this Thread to Solved.....thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks