• We are receiving reports of members using the private messaging service (Conversations) in ways that break the forum rules:
    • Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.
    • Soliciting business for yourself is not permitted. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Likewise, members seeking solutions must not offer compensation for them. If you have an urgent need, check the Consulting Services page.
    Please help us out by clicking the Report link on any messages you receive that violate these rules. Thank you
  • If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.

Need help on formatting cell value

khuharshree

New Member
Joined
May 11, 2018
Messages
3
Hi Friends,

I am looking for help to convert values

FROM:
05-01-2018-01.15

<tbody>
</tbody>

TO:

05/01/2018 01.15 AM


Appreciate your help on this

Thanks
Khuharshree
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
I doubt thats a value, more likely a text date/time.
Where does it come from?
test with =isnumber(cell-ref)...FALSE indicates text and it will need to be converted

If it IS text, you could probably do it with SUBSTITUTE()
 

khuharshree

New Member
Joined
May 11, 2018
Messages
3
Hi Ford,

Thanks for your reply. Actually I am running script which further gets me values in above format i.e. 05-01-2018-01.15 which I needs to covert it to 05/01/2018 01.15 AM

In short I am trying to get Stop Time and Start Time in this format
05/01/2018 01.15 AM . This will allow me to use MINUTE formula to calculate downtime.
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,016
Office Version
365
Platform
Windows
Welcome to the forum.

I have assumed all of that data will be of the format month-day-year-time, all of them being exactly 16 characters long. Put 05-01-2018-01.15 in cell A1 and this formula in your worksheet:

Code:
=DATEVALUE(LEFT(A1,10))+RIGHT(SUBSTITUTE(A1,".",":"),5)
Then use a custom number format with exactly this as the Type: mm/dd/yyyy hh:m AM/PM.
 
Last edited:

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,016
Office Version
365
Platform
Windows
Super, I'm glad it worked. You're welcome.
 

Forum statistics

Threads
1,077,961
Messages
5,337,425
Members
399,145
Latest member
SPLhorses

Some videos you may like

This Week's Hot Topics

Top