unable to convert text to date format using date value

[pratik salian]

Hi All

I have 2 questions related to dates.

1: Im trying to convert the text Aug 07 2014 08:57 PM into a date format recognized by excel. I tried using the date value function without any success

2: Once I am able to get the date in the right format I need to calculate the time difference . Based on whether the date is a weekday or weekend, i need to calculate the time left. I wanted to know the approach to solve this problem. Should I do it with VBA or excel forumales are enough.

Please help

 

[Rick Rothstein]

Hi All

I have 2 questions related to dates.

1: Im trying to convert the text Aug 07 2014 08:57 PM into a date format recognized by excel. I tried using the date value function without any success

2: Once I am able to get the date in the right format I need to calculate the time difference . Based on whether the date is a weekday or weekend, i need to calculate the time left. I wanted to know the approach to solve this problem. Should I do it with VBA or excel forumales are enough.

Please help

I am about to go to sleep, so I don't have time to look at #2, but try this formula for #1...

=0+REPLACE(A1,7,0,",")

You will have to format the cell as a Date with Time in order to get it to display "correctly".

 

[pratik salian]

I am about to go to sleep, so I don't have time to look at #2, but try this formula for #1...

=0+REPLACE(A1,7,0,",")

You will have to format the cell as a Date with Time in order to get it to display "correctly".

thnks rick..That worked

I have arranged rows of such dates in ascending order. Now i want to detect the dates/times that is post 3:00:00 PM time.. Ne vba code/ formulae to achieve this?