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

Thanks!
Rowby
EE-GOOD-Date-and-time-for.xlsx
EE_-BAD-date-and-time2.xlsx
SpreadsheetsMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Peter Hall

8/22/2022 - Mon
Karen Falandays

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 Goren

ASKER
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 Hedegaard

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Karen Falandays

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 Goren

ASKER
Looks good Karen.  I’ll be testing it tonight or tomorrow morning.  I’m sure it will work,   ....Rowby.
Karen Falandays

Keep me posted!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rowby Goren

ASKER
Hi Karen,

Trying it out now!   Stay tuned...

Rowby
Rowby Goren

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
Karen Falandays

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rowby Goren

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

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 Goren

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rowby Goren

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
Karen Falandays

Now you need to format as dates or times
Rowby Goren

ASKER
Ah!  Thanks Karen. I'll try that :)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rowby Goren

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
Karen Falandays

Best of luck
Rowby Goren

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???
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rowby Goren

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.

Formattingee-version-time-formatting.xlsx
Ejgil Hedegaard

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 Goren

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

Best
Rowby
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Peter Hall

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rowby Goren

ASKER
Thanks everyone. Sorry I had missed awarding the points!
Peter Hall

Thank you Rowby.