Need help on formatting cell value

[khuharshree]

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]

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]

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]

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:

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

 

[khuharshree]

Thanks a lot DrSteele ..it works as you have mentioned .. You are a Gem

 

[DRSteele]

Super, I'm glad it worked. You're welcome.