Adding Hours to Date + Time Text Data

**ExcelQuestFL** · 10-22-2012, 05:42 PM

Hello,

Working with time in Excel can be a killer sometimes! ANY and ALL assistance would be so much appreciated

I have data that is text and includes a date and time. I want to add 5 hours to the time, and if it rolls to the following day, then I want that day included.

Currently, I use text to columns and have 1 column with a numerical Date, and 1 column with a text Time. I use a formula to add 5 hours to the time, but if I roll to the following day, I have no idea it happened.

This is what my original data looks like - 08/12/2012 22:07:09:328
And this is the formula I use to add 5 hours after the text to column - =TIME(MID(C3,1,2),MID(C3,4,2),MID(C3,7,2))+TIME(5,0,0)

My desired result is to have 1 Column with the new date if it happened and 1 Column with the time 5 hours ahead. I've attached a sample of what the data looks like with desired result if rolling to a new day - T +5.xlsx

I think this will be a breeze for some of you, but I've literally spent hours trying different approaches and the above is the closest I've gotten

EDIT - Or I would be open to keeping the date and time stamp in 1 one cell, adding the 5 hours and then splitting it out. But I'm not sure how to do that..

**Nels** · 10-22-2012, 07:37 PM

You can try the followings:

B2(Date Column):
=

Please Login or Register  to view this content.

C2(Time Column):

Please Login or Register  to view this content.

**ExcelQuestFL** · 10-22-2012, 07:45 PM

Thank you so much for the help! Your solution works well. What if I have a single day digit? So A3 may look like - 08/9/2012 12:07:09:328.

EDIT - maybe I could incorporate a len function? If len > 23, then do this, else that. But could get quite messy.

**Nels** · 10-23-2012, 12:44 PM

Better not consider if len > 23 as there's several source to cause this. I agree it will become messy.

Try this, the formula is a bit longer:
B3:

Please Login or Register  to view this content.

C3:

Please Login or Register  to view this content.

This time the formula handles if the day/month is signle digit.

**Nels** · 10-23-2012, 01:07 PM

If the answer solves your case,
Can you do me a favor to reduce this forum admin's work?

You can marked this thread as SOLVED, and clicking the small star icon low left of my answer to add my reputation.

**daddylonglegs** · 10-23-2012, 01:39 PM

Your text value is almost in a valid date/time format except for the 3rd ":" so this formula will give you the date (with 5 hours added)

=INT(SUBSTITUTE(A3,":",".",3)+"5:00")

and this one the time

=MOD(SUBSTITUTE(A3,":",".",3)+"5:00",1)

format each cell in required date or time format