EXCEL Combine Date and Time in 1cell

March 1, 2011 at 08:27:02
Specs: Windows XP
Hey!

I cannot find a solution for this and i know there are some smart guys out there :)

What i need to do is combining a date and a time in 1 cell (but not adding them)

Example:
A1: 01/03/2011
A2: 14:00

what i would need now is having it one cell which would be:

A3: 01/03/2011 14:00

Thanks a lot in advance!

Manuel


See More: EXCEL Combine Date and Time in 1cell

Report •


#1
March 1, 2011 at 12:10:43
Try:

=A1+A2

Provided A1 is formatted as a Date and A2 is formatted as a Time.

MIKE

http://www.skeptic.com/


Report •

#2
March 2, 2011 at 06:15:44
Thanks for the fast reply!
I got stuck again though... What I need is to get the difference in hours between any to dates and times:

Example:
A1: 01/03/2011 14:00
A2: 02/03/2011 18:00
Outcome: A3: 28:00 (which would be 28 hours difference).

On top of that it shouldnt take the weekends into consideration... so in the calculation Monday comes after friday...
This is a massive formula I got to do - thats just a part of it where I couldn't find a solution...

Thanks a lot in advance!


Report •

#3
March 2, 2011 at 07:21:08
Example:
A1: 01/03/2011 14:00
A2: 02/03/2011 18:00
Outcome: A3: 28:00 (which would be 28 hours difference).

Actually it's a lot more than 28 hours, closer to 748 hours.

On top of that it shouldnt take the weekends into consideration... so in the calculation Monday comes after friday...

Perhaps before you begin, you might want to read this:

http://www.cpearson.com/excel/datet...

It will give you a better understanding of what it is your trying to accomplish and the possible problems you may run into.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 2, 2011 at 07:42:48
@Mike
Assuming the OP uses the date format dd/mm/yyyy, the difference is 28 hours.

Nigel

Mobo: Asus P7P55D LE
OS: Microsoft Windows 7 Professional OEM
CPU: Core i5 750 @ 2.67 GHz
RAM: Corsair Dominator DHX+ DDR3 1600MH 4GB
GPU: Sapphire 4870 D


Report •

#5
March 2, 2011 at 08:18:08
Nigel:

January 3rd 2011 2:00 PM

is quite a bit more than 28 hours from

February 3rd 2011 6:00 PM.

but, since I'm using US formatted time, all bets are off.

I wish you guys across the pond would do things the right? way.......

Thanks for the heads up on the date.

.

MIKE

http://www.skeptic.com/


Report •

#6
March 2, 2011 at 11:27:39
Thanks anyways.. I'll figure it out somehow.

There is no answer for the question which time format is right or wrong but if you think about it objectively you will have to admit that ours is the more logical format and the way you would invent it :) To late though.. you guys got the wrong? one ;)

cheers


Report •

#7
March 2, 2011 at 12:36:57
I'll figure it out somehow.

I take it from your question that you are attempting to create some type of
time sheet / schedule program.

I'm not one to re-invent the wheel, so here are some sites with Timesheet/Schedule spreadsheets you can download and review. There are a great many on the web these are just a few.

When you find the one that most fits your needs, if you need help in modifying it come back we'll try to see what we can do.


http://office.microsoft.com/en-us/t...

http://www.vertex42.com/ExcelTempla...

http://www.dovico.com/tips.aspx

MIKE

http://www.skeptic.com/


Report •

Ask Question