do loop to copy date and append time at the end

sufianmalik

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

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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
122
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,523
Messages
5,602,164
Members
414,508
Latest member
Yaya1900

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