do loop to copy date and append time at the end

[sufianmalik]

Hi there

i have two workbooks. i need to copy a date cell from one workbook to another and after the date append the time.

whats the easiest way to append the time to the end. for example in A1 date says 30/03/2009, in destination cell would like 30/03/2009 17:00

Thanks for your help

Sufian

 

[Mike Blackman]

Hi,

Assuming that your dates and your times are true numbers, check using =ISNUMBER() then I'd simply add them together.

=A1+B1

 

[sufianmalik]

Mike, the problem is the time - unless i'm mistaken

you see i would like to export this data to another application which displays time in hh:mm the seconds will cause trouble on the import.

it would be one random time that we want to add to the cell - probably 17:00

the data would have an ' apostrophe before it making it text so would be like this '30/03/09 17:00

thanks

 

[Mike Blackman]

Hi,

If your combining the cells in Excel then you would have;

=A1+0.708333333333333

With 0.708333333333333 being the numeric value of 17:00:00, the cell format can be set to dd/mm/yy hh:mm which would give you the value of;

01/01/09 17:00 in the cell.

If you want it to be treated as Text then maybe;

=TEXT(A1+B1,"dd/mm/yy hh:mm")

If the values your dealing with in Excel are treated as text due to them already having an apostrophee maybe;

=(SUBSTITUTE(A1,"'","")+0)+(SUBSTITUTE(B1,"'","")+0)

If your trying to add an apostrophee to the date & time simply;

="'"&A1+B1