Date Conversion

[bobsri]

Hi All,


I have generated a report from SAP and in the report we have Start date in H2 cell (Nov 02, 2012 01:48AM) and End Date in I2 cell (Nov 07, 2012 06:33AM) i need to count #of days in between start and end dates.


for this i am using =DAYS360(H2,I2,FALSE)but the problem is the date format is in general and i am not able to conver the format from general to Date format because of this i am getting #VALUE! error.


Can you any one help me out on this.


Thanks,

Bob

 

[Kevin@Radstock]

Hi bobsri


If the dates are "(Nov 02, 2012 01:48AM)", they are text and you will need to extract the dates

 

[lhkittle]

Hi bobsri,


=LEFT(I1,12)-LEFT(H1,12)


returns 5.


Regards,

Howard

 

[Hui]

Bob


Firstly, Welcome to the Chandoo.org Forums


You could add a helper column with a formula like:

=DATE(MID(H2,FIND(", ",H2)+2,4),MONTH(LEFT(H2,3)&1),MID(H2,5,2))

to convert your date to a real Date value

 

[bobsri]

Thank you all!


Hui you are awsome. can we convert the value to dd/mm/yyyy hh:ss format because as of now i am getting only dd/mm/yy format only.


Thanks for your cooperation on this.


Regards,

Bob

 

[Hui]

=DATE(MID(H2,FIND(", ",H2)+2,4),MONTH(LEFT(H2,3)&1),MID(H2,5,2)) + TIMEVALUE(MID(A1,14,5)&" "&RIGHT(A1,2))

 

[bobsri]

Perfect!


Thanks a ton for your cooperation on this Hui!


i would like to improve my excel skills can you please guide me.


thanks again, Bob

 

[Hui]

in regards to improving Excel skills

1. Practice, practice, practice

2. Have 1 good Excel book as a Reference

3. Maybe go on a few simple courses