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
LVL 9
Rowby GorenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Karen FalandaysTraining SpecialistCommented:
No formula needed, this is a formatting issue. Copy the start and end data to a new column. Go to number formatting (click the dialog box launcher to get to the full formatting dialog box). Choose your desired date format and desired time format. Let me know if you need assistance with this. I have attached a revised "bad" ss
rev-EE_-BAD-date-and-time2--1-.xlsx
Rowby GorenAuthor Commented:
Hi Karen.

 
Dialog box
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
Ejgil HedegaardCommented:
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
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Karen FalandaysTraining SpecialistCommented:
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?
Rowby GorenAuthor Commented:
Looks good Karen.  I’ll be testing it tonight or tomorrow morning.  I’m sure it will work,   ....Rowby.
Karen FalandaysTraining SpecialistCommented:
Keep me posted!
Rowby GorenAuthor Commented:
Hi Karen,

Trying it out now!   Stay tuned...

Rowby
Rowby GorenAuthor Commented:
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
Karen FalandaysTraining SpecialistCommented:
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
Rowby GorenAuthor Commented:
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 .Screen capture   I've also atttached an excel file for you to look at. Thanks!
For-Karen-again-EE.xlsx
For-Karen-again-EE.csv
Karen FalandaysTraining SpecialistCommented:
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.
Rowby GorenAuthor Commented:
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
Rowby GorenAuthor Commented:
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
Karen FalandaysTraining SpecialistCommented:
Now you need to format as dates or times
Rowby GorenAuthor Commented:
Ah!  Thanks Karen. I'll try that :)
Rowby GorenAuthor Commented:
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
Karen FalandaysTraining SpecialistCommented:
Best of luck
Rowby GorenAuthor Commented:
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???
Rowby GorenAuthor Commented:
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.

Formattingee-version-time-formatting.xlsx
Ejgil HedegaardCommented:
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.
Rowby GorenAuthor Commented:
Ah yes, you are right. I just looked at the sample csv that we must match and I see the seconds.

Best
Rowby
Peter HallSpreadsheet SpecialistCommented:
Hi Rowby, I don't know if you are still looking for a solution for you problem, however I wanted to share my 2 cents with you.  I was able to complete the task in my example in under 1 minute.   I simply created 4 column headings as you will see in the attachment.  In first two I copied the "Start" over from the left under both "Start Date" and "Start Time" to the right Then simply format the Start Date column for Date (11/11/2019) and then Start Time for Time (12:30 AM).  Then repeated this for End Date and End Time.  It was very quick.  Do you find this useful going forward?
date-and-time.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rowby GorenAuthor Commented:
Thanks everyone. Sorry I had missed awarding the points!
Peter HallSpreadsheet SpecialistCommented:
Thank you Rowby.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.