Hi there,
I have two issues with date/time formatting that I'd appreciate some feedback on. I have a formula that rearranges data from a DDE link into the proper date/time format however, Excel is not recognizing it as a date/time. I need it to recognize both the date and the time, but DateValue or TimeValue only formats as one or the other. The DDE link returns the date/time in the following format:

2013.04.08 08:01

and the formula returns it as:

04/08/2013 08:01:00

Formula: copy to clipboard 
=IF(C20=" ","",TEXT(SUBSTITUTE(MyDDE4|Time_1,".","/")+0,"mm/dd/yyyy hh:mm:ss"))


Also, the time that I am receiving from the link is 7 hours ahead of my local time. I need the time to be subtracted by 7 hours. This seems to be tricky...

Any feed back is greatly appreciated.

Thanks!