Link to home
Start Free TrialLog in
Avatar of Rowby Goren
Rowby GorenFlag for United States of America

asked on

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.  

Avatar of Karen Falandays
Karen Falandays
Flag of United States of America image

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
Avatar of Rowby Goren


Hi Karen.

User generated image
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.


The date is the integer part of date/time
And the time is the fraction

Correct display is formatting.
See attached.
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?
Looks good Karen.  I’ll be testing it tonight or tomorrow morning.  I’m sure it will work,   ....Rowby.
Keep me posted!
Hi Karen,

Trying it out now!   Stay tuned...

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.   ???

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

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 .User generated image   I've also atttached an excel file for you to look at. Thanks!
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.
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.

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
"  :)

Now you need to format as dates or times
Ah!  Thanks Karen. I'll try that :)
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!

Best of luck
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???

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.

User generated imageee-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.
Ah yes, you are right. I just looked at the sample csv that we must match and I see the seconds.

Avatar of Peter Hall
Peter Hall

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks everyone. Sorry I had missed awarding the points!
Avatar of Peter Hall
Peter Hall

Thank you Rowby.