Can anybody tell me how to convert a text field received as dd-mm-yyyy hh:mmPM to dd/mm/yyyy hh:mm:ss. I have to convert the AM/PM and express the time in 24h format.
An example:
Text field shows 08-Jan-2020 6:02PM (notice that there is no space between the 2-digit minutes and "PM") and I want to have a time and date field, that I can use for calculation, as 08/01/2020 18:02:00
Thank you for your help.
Are you willing to break it down thru a few cells?
Data Range
B1 >> =LEFT(A1,FIND(" ",A1)-1)+0
C1 >> =MID(A1,FIND(" ",A1)+1,LEN(A1))
D1 >> =IF(OR(RIGHT(C1,3) =" PM",RIGHT(C1,3) = " AM"),C1,LEFT(C1,LEN(C1)-2)&" "&RIGHT(C1,2))+0
You could even combine C and D
HTH
Regards, Jeff
Thank you for the response. Is there a way to combine the date (mm/dd/yyyy) with the time in one single cell in the end?
Also, I noticed that I am getting an "#VALUE" error on the formula for column D, especially when the original time has only 1 digit for the hour time (1:00PM) instead of (01:00PM)Attachment 659305
How about an attachment vice a picture.
You could also try this.
Select the column:
Find >> "PM"
Replace >> " PM"
If needed
Find >> "AM"
Replace >> " AM"
This is without the quotes. Now you can use =INT(A1) and =MOD(A1,1)
Format as you require
The attachement button doesn't work on my web browser so here is a link to my sheet: tinyurl.com/t59towe
On the first tab, I used the first formulas you had posted showing the error.
On the second tab, I used what you suggested but I still don't know how to get the date and time together in one cell. Does that make sense?
The date and time is in one cell on Tab2. I don't understand why you can't use what you have on the second tab?Originally Posted by kevinbp
I must be missing something in which you desire. On the second tab, highlight column B and C and then select Ctrl + 1. You will see these are numbers available to do this calculations you wish to do.
Last edited by jeffreybrown; 01-23-2020 at 06:17 PM.
Ctrl + 1 does not work for me, I must be doing something wrong. On tab 2, column 3, the date format does not match the date in column A, it brings the date to 1900.Attachment 659327
Select column B and C and select Format Cells. This is the same as Ctrl + 1. When in format cells, select any format you desire.
As for column C, this is using the MOD function which returns everything to the right of the decimal.
This >> 08-Jan-2020 6:02PM >> to Excel is actually 43838.7513888889
The date and time is just a mask for the number.
43838 is the date and .7513888889 is the time.
The function INT will extract everything to the left of the decimal and MOD extracts the right with the period.
In Excel, 24 hrs equals 1; therefore; .75 is three quarters of a day. >> 6:00 PM
Does this help?
http://cpearson.com/excel/datetime.htm
Yes, that did help. I was able to format both columns B and C the way I wanted it. Now, how do I combine the 2 into one cell without messing up the formats?
Again, I must ask, why do you need to combine the two when it's already combined in column A?
True, my mistake, I must have been confused. Thank you for your help! That worked!
No worries Kevin. Glad you have your solution now and you are very welcome.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks