Excel Experts - I need a formula to change the format of time. And also to split date and time into separate columns.

Rowby Goren
Rowby Goren used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Karen FalandaysTraining Specialist

Commented:
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

Author

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
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Karen FalandaysTraining Specialist

Commented:
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?

Author

Commented:
Looks good Karen.  I’ll be testing it tonight or tomorrow morning.  I’m sure it will work,   ....Rowby.
Karen FalandaysTraining Specialist

Commented:
Keep me posted!

Author

Commented:
Hi Karen,

Trying it out now!   Stay tuned...

Rowby

Author

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 Specialist

Commented:
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

Author

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 Specialist

Commented:
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.

Author

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

Author

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 Specialist

Commented:
Now you need to format as dates or times

Author

Commented:
Ah!  Thanks Karen. I'll try that :)

Author

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 Specialist

Commented:
Best of luck

Author

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

Author

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

Author

Commented:
Ah yes, you are right. I just looked at the sample csv that we must match and I see the seconds.

Best
Rowby
Spreadsheet Specialist
Commented:
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

Author

Commented:
Thanks everyone. Sorry I had missed awarding the points!
Peter HallSpreadsheet Specialist

Commented:
Thank you Rowby.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial