do loop to copy date and append time at the end

sufianmalik

Board Regular
Joined
May 7, 2002
Messages
128
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi,

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

=A1+B1
 

sufianmalik

Board Regular
Joined
May 7, 2002
Messages
128
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,282
Messages
5,836,381
Members
430,425
Latest member
xlsee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top