Excel Experts - I need a formula to change the format of time. And also to split date and time into separate columns.
Hello Excel Experts
I have a spreadsheet that includes 4 columns that involve date and time.
1 need formulas to do the following things.
In the "Bad" spreadsheet the date and times are in the same column I need the date column to contain only the date, with the time copied over to the adjoining column.
Also the time format is currently (24 hour time) like this format " 9/23/2017 5:00" and 4/1/2015 15:00
I need it changed to 10:00 AM and 3:30 PM format (based on 24 hour time)
I think the two attached spreadsheets help explain all of this. The "Bad" spreadsheet is the one that includes the time combined in the date columns. The "Good" spreadsheet shows the times copied over to the adjoining column, including the AM and PM based on 24 hour clock.
NOTE: As you can see, there are 4 columns affected. START DATE, START TIME, END DATE, END TIME
I can temporarily copy and paste the Bad columns into the Good spreadsheet if it makes the formula easier.
Thanks!
Rowby
EE-GOOD-Date-and-time-for.xlsx
EE_-BAD-date-and-time2.xlsx
I have a spreadsheet that includes 4 columns that involve date and time.
1 need formulas to do the following things.
In the "Bad" spreadsheet the date and times are in the same column I need the date column to contain only the date, with the time copied over to the adjoining column.
Also the time format is currently (24 hour time) like this format " 9/23/2017 5:00" and 4/1/2015 15:00
I need it changed to 10:00 AM and 3:30 PM format (based on 24 hour time)
I think the two attached spreadsheets help explain all of this. The "Bad" spreadsheet is the one that includes the time combined in the date columns. The "Good" spreadsheet shows the times copied over to the adjoining column, including the AM and PM based on 24 hour clock.
NOTE: As you can see, there are 4 columns affected. START DATE, START TIME, END DATE, END TIME
I can temporarily copy and paste the Bad columns into the Good spreadsheet if it makes the formula easier.
Thanks!
Rowby
EE-GOOD-Date-and-time-for.xlsx
EE_-BAD-date-and-time2.xlsx
ASKER
Hi Karen.
Not sure what I should enter -- and not sure how I split the date anad time into two columns. -- like you did in your attachment.
I just need a little more clarification, step by st ep.
Thanks!
Rowby
Not sure what I should enter -- and not sure how I split the date anad time into two columns. -- like you did in your attachment.
I just need a little more clarification, step by st ep.
Thanks!
Rowby
The date is the integer part of date/time
=INT(A2)
And the time is the fraction
=MOD(A2,1)
Correct display is formatting.
See attached.
EE_-BAD-date-and-time2-with-formula.xlsx
=INT(A2)
And the time is the fraction
=MOD(A2,1)
Correct display is formatting.
See attached.
EE_-BAD-date-and-time2-with-formula.xlsx
Insert two columns for your start time and end time. Copy the Start date and end date columns. I didn't need to split. When you format your date columns, choose any date format from the date category that you like. When you format your time column, format the time from the time category any way you like! If you look at my example, when you click on any of those cells and look into the formula bar, you will see the entire date/time.
Does that make sense?
Does that make sense?
ASKER
Looks good Karen. I’ll be testing it tonight or tomorrow morning. I’m sure it will work, ....Rowby.
Keep me posted!
ASKER
Hi Karen,
Trying it out now! Stay tuned...
Rowby
Trying it out now! Stay tuned...
Rowby
ASKER
Hi Karen
Got it working, thanks. Now how do I copy and paste them so the paste is not dependent on the formula. Just the hard numbers. I thought I could do it by pasting Values, but i see that doesn't seem to make the numbers permanent. ???
Best
Rowby
Got it working, thanks. Now how do I copy and paste them so the paste is not dependent on the formula. Just the hard numbers. I thought I could do it by pasting Values, but i see that doesn't seem to make the numbers permanent. ???
Best
Rowby
Wait, I didn't see a formula? You can ignore the Int and Mod formulas. Simply copy your data from the start column and paste it into the start time column. Do the same for the End column and then format each column appropriately
ASKER
Hi
Right, there was no formula.
But look at the attached. I used the formatting to get what I want. But when I click on a date or time I see the old info still showing in the top formula column. I want to be able to send this form to someone with the date and time more permanent. Such as a csv file .
I've also atttached an excel file for you to look at. Thanks!
For-Karen-again-EE.xlsx
For-Karen-again-EE.csv
Right, there was no formula.
But look at the attached. I used the formatting to get what I want. But when I click on a date or time I see the old info still showing in the top formula column. I want to be able to send this form to someone with the date and time more permanent. Such as a csv file .
I've also atttached an excel file for you to look at. Thanks!For-Karen-again-EE.xlsx
For-Karen-again-EE.csv
Tell me more, Rowby: When you send it to another, will they be editing or adding data? Will you need to do any date and time calculations? If either of those are true, you may want to keep it exactly as it is. If you prefer the data to display as just the date and time, then go ahead and use the INT and MOD formulas and paste values.
ASKER
They will not be editing or adding data. But they need to receive it in a csv file.
So i will try those formulas.
I probably won’t get to it until tomorrow morning.
Rowby
So i will try those formulas.
I probably won’t get to it until tomorrow morning.
Rowby
ASKER
Hi everyone.
I'm trying to paste Ejgil Hedegaard's formula as the resulting values into my "red" column, but something completely different shows up.
I know I've been able to do this several years ago, but take a look and please let me know what I am doing "wrong".
In the attached spreadsheet I tried to paste the Values from Column C into the Green Column G But all I see are numbers like "43009
" :)
Thanks!
EE-Pasting-Value-Upload.xlsx
I'm trying to paste Ejgil Hedegaard's formula as the resulting values into my "red" column, but something completely different shows up.
I know I've been able to do this several years ago, but take a look and please let me know what I am doing "wrong".
In the attached spreadsheet I tried to paste the Values from Column C into the Green Column G But all I see are numbers like "43009
" :)
Thanks!
EE-Pasting-Value-Upload.xlsx
Now you need to format as dates or times
ASKER
Ah! Thanks Karen. I'll try that :)
ASKER
That did it. Thanks for being so patient with me. At least I learned something.. I probably won't need to do this for another 10 years so look for an update on this question in 2029!
I'll be awarding points later today or tomorrow. Got to get this off to my friend!
Rowby
I'll be awarding points later today or tomorrow. Got to get this off to my friend!
Rowby
Best of luck
ASKER
Quick question. I guess I could test this myself but I’m not at home.
Why the able to export this Excel file as a CSS and the dates and times will export in the date and time for my life selected?
I think I read somewhere there’s a way to export as text??
Or I think there may be utility add on for excel which can export as text without changing the date and time formatting???
Why the able to export this Excel file as a CSS and the dates and times will export in the date and time for my life selected?
I think I read somewhere there’s a way to export as text??
Or I think there may be utility add on for excel which can export as text without changing the date and time formatting???
ASKER
Hi
Little glitch in the time formatting.
Using the cell formatting option I got the time to display in the correct format on the visual display. But it is adding seconds in the export.
I looked at the custom way to do it, and it's not showing the seconds -- so I am not sure why the seconds are showing up in the formula area -- and in the css export.
ee-version-time-formatting.xlsx
Little glitch in the time formatting.
Using the cell formatting option I got the time to display in the correct format on the visual display. But it is adding seconds in the export.
I looked at the custom way to do it, and it's not showing the seconds -- so I am not sure why the seconds are showing up in the formula area -- and in the css export.
ee-version-time-formatting.xlsx
The real value of 4:00 AM is 0.166666666664241
In the formula line it is displayed as standard with seconds, but in the cell it is displayed with the format you use.
Similar, if you use a 2 decimal display of 1.23456 it will be 1.23, but in the formula line it will show the real value 1.23456.
In the formula line it is displayed as standard with seconds, but in the cell it is displayed with the format you use.
Similar, if you use a 2 decimal display of 1.23456 it will be 1.23, but in the formula line it will show the real value 1.23456.
ASKER
Ah yes, you are right. I just looked at the sample csv that we must match and I see the seconds.
Best
Rowby
Best
Rowby
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks everyone. Sorry I had missed awarding the points!
Thank you Rowby.
rev-EE_-BAD-date-and-time2--1-.xlsx