Combine time & date cells

... /
September 16, 2009 at 01:39:23
Specs: XP Home/Pro, 1gig
I want to combine A2&B2 cells
A2 has date format 9/23/09
B2 has time format 10:48 AM
When I combine them I get a decimal number!
I want it to look like this 9/23/09 10:48 AM
I tryed formula A2&" "&B2 but that's not what I'm looking for.
Oh ya ..... excel 2003

See More: Combine time & date cells

Report •

September 16, 2009 at 04:57:25



In the "Type:" field enter (paste) this:

mm/dd/yy hh:mm AM/PM

Report •

September 16, 2009 at 05:01:17

In Excel you must remember that there there is a difference between what Excel holds as a value and what shows in a cell based on formatting.

The two cells with a date and a time that you refer to (A2 & B2), both contain numbers, but the format applied makes them look like a date or a time.

Excel stores the date and time as a decimal number. The integer (whole number) part is the date. Add 1 to todays date will give you tomorrow's date.

Time is in the decimal part. 0.5 is noon, i.e.,half way through the day.

If you enter some text that Excel recognizes as a date, it will store a whole number, but format the cell as a date. The same for time - enter "12:00" which Excel recognizes as a time and it will store 0.5, but display 12:00 and in the formula bar it will show 12:00:00 PM

So Excel is pretty clever
- recognizes dates and times,
- converts them into numbers that allow calculations and
- formats them in ways that you recognize as a date or time.

The date system typically starts at 01-January-1900 and adds one for every day since then.

What Excel recognizes as dates or times and what it displays may vary depending on what has been setup on your system and may vary in different languages (but I have no experience of that).

Here's what you need to do:
In cell A2 change the format to number with two decimal places.
If the cell still shows 09/23/09 then Excel didn't recognize your entry as a date.
So enter the date again as 23-Sep-09.
With the number format it will show as 40079.00
Now format it as a date in a style that suits you.

In B2 (10:48 AM), format the cell as a number and it should show as 0.45
Format it back to a suitable time format.

Now in cell C2 enter A2+B2

Change the format in cell C2 to a custom format as follows:
dddd, dd mmmm yyyy \at h:mm AM/PM
Cell C2 will now show:
Wednesday, 23 September 2009 at 10:48 AM

(You will have to increase the width of column C to show the above, else you will just get ######).

In Cell C3 enter the formula =C2+1.5
and it will show:
Thursday, 24 September 2009 at 10:48 PM
(note that the time is now PM due to the 0.5 you added)

One more issue about adding cells with dates and times - If a cell is formatted to show only the date or only the time, you will not know if the date cell also contained time information (other than.00) or that the time information contained date information other than 0.
he preferable solution is to use data entry control to ensure that dates are only integers and that times are all less than 1.

Alternatively when adding a date and time add the relevant integer and decimal values.


PS you asked a question about sorting rows - did the solution given work or not?

Report •

Related Solutions

Ask Question