Adding Time to a date cell

[Avinit]

Hi,

I am trying to create a formula that can automatically add the time to the date in column A. I have the right custom number category at it works if I copy and paste it, I just have 200 plus to do so I need a formula to do it.

Column A Column B

01/01/10 09:34:00
02/01/10 10:24:00
03/01/10 11:00:00

I have tried and failed many A2+B2 type formaulas but am completely confuddled.

Any help would be greatly appreciated

Thankyou Ben

[dominicb]

Good afternoon Avinit

Try something like :
=VALUE(A1&B1)

In the case of your first example, this would return 401790.3986 - then just format it as required, for example a custom format of dd/mm/yy hh:mm:ss should do it for you.

HTH

DominicB

[Avinit]

Thanks for the quick reply Dominic

When I try what you have suggested I can seem to get it to work. It comes out with the a VALUE error.

I have tried changing the formats of the numbers (and see that 01/01/10 is 40179) but it doesnt seem to want to add them together for me.

I am probably doing something stupid, but still cant figure out why.

Thanks

Ben

[squiggler47]

I think if you try =value(a1&" "&b1)

or just

=--(a1&" "&b1)

[martindwilson]

a1+b1 works for me
format both cells as general what do they say? if they both show numbers 40179 and 0.398611111
than a simple add works fine

[squiggler47]

I'm assuming at least one of the values is text rather than a time/date

[arthurbr]

Attaching a sample of your data might help

[daddylonglegs]

If your values are valid times and dates (or even text-formatted without additional characters) then

=A2+B2

should work fine. Looks like the date is valid so perhaps the time isn't. Try

=A2+MID(B2,FIND(":",B2)-2,8)

format as dd/mm/yy hh:mm:ss or similar

[Avinit]

Thanks for all your help guys.

I had inherited this sheet from someone else and as such didnt know what form the data was in.

Trying your suggestions on a blank sheet worked fine, so I had another look at what was in my original sheet and there the seconds were input as a decimal place instead of :

Thanks to all for your help.

Its sometimes the simple things you dont see that provide the biggest challenges.