Merge date and time columns without loosing time

**robhargreaves** · 11-25-2006, 04:13 PM

Hi I would like to merge a colum with a date and a column with a time into one to show DD/MM/YYYY HH:MM

I have tried merge cells and it takes out the time and leaves the date only.

Thanks

Rob

**Bryan Hessey** · 11-25-2006, 04:39 PM

Originally Posted by robhargreaves

Hi I would like to merge a colum with a date and a column with a time into one to show DD/MM/YYYY HH:MM

I have tried merge cells and it takes out the time and leaves the date only.

Thanks

Rob

Hi,

Just add the two, as

=INT(A1)+MOD(B1,1)

where A1 is the date and B1 the time, then adjust the cell format of the answer to show date & time.

hth
---

**robhargreaves** · 11-25-2006, 04:53 PM

Thanks thats great, much appreciated

**Bryan Hessey** · 11-26-2006, 07:47 AM

Originally Posted by robhargreaves

Thanks thats great, much appreciated

Hi,

Thanks for the response.

**sigma69pi** · 11-07-2013, 05:43 PM

Solved my problem 7 years later....

**Krista1984** · 07-11-2014, 08:47 PM

Hi there,

Thanks for the above formula. I applied it to my data and it works for some cells and not others.

e.g. one cell will work perfectly - 1/07/2010 11:57, and the cell below will come up as #VALUE!

Despite this both the date and time columns for the correct working cells and non working cells are in the same format (date 14/03/01; time 13:30:55). Does anyone know how I can solve this problem?

**Tony Valko** · 07-11-2014, 09:20 PM

You should stay in your own thread...

https://www.excelforum.com/showthread.php?t=1024082

Some moderators will even "yell" at you for posting your question in someone else's thread!

**alansidman** · 07-11-2014, 09:25 PM

@Krista1984

Tony is right.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.