Excel combine date and time fields 2

[jmk418]

Hi,
I have two columns in excel one has a date (ex 10/24/2009) and the other a time (ex 14:24). I want to combine those fields into a datetime format so that I can compare it to another datetime and calculate the hours and minutes difference between them. I thought this would be pretty simple but I have not had any success.
Thanks in advance.
Jeremy

 

[jmk418]

UPDATE: I am able to simply combine the two fields together and get a datetime column with custom formatting m/d/yyyy h:mm. I would like to be able to calculate the difference in hours without having to create 2 additional columns in the spreadsheet then taking difference in hours from those 2 columns.

 

[SkipVought]

Hi,

The FIRST question is, do you have REAL DATE and REAL TIME values?

To determine that, select your date and time ranges and change the Cell Format to GENERAL. If your display values change to NUMBERS, then you have real date/time values, in which case, to combine a date and a time, you merely ADD the values together and then FORMAT the result to DISPLAY the date/time as you want to view it.

If not, you must convert your TEXT to a numeric date or time value.

1. Enter a 1 in an empth cell
2. COPY this cell
3. select your date or time range
4. Edit > Paste Special -- VALUES

Format as desired.

Read and understand: faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jmk418]

Thanks for the response. I can get that far. But I want to be able to format within a single cell. I have real numbers. For the example above when I convert them to general format they are 40110 and 0.6.

Here is an example of what I want to do:

A B C D E
Date1 Time1 Date2 Time2 Hours Difference
1 10/24/09 13:22 10/24/09 14:24 =Hour((C1+D1)-(A1+B1))
2 11/16/09 22:42 11/17/09 0:02 =Hour((C2+D2)-(A2+B2))

Results:
E1: 1
E2: -22

The code works fine for hour difference unless the time rolls over midnight and minute code displays negative numbers in the hour rolls over. I think this is because the values are not in date time format.
Any help is appreciated.
Thank you.
Jeremy

 

[PeterMoran]

Hi,

I have your data in my s'sheet with col E formatted as Custom(More Formats) with "[h]:mm". (The braces here keeps the result in hours and mins even if it is more than a day (24 hours).

This gives me the following answers:

Row 1: 1:02
Row 2: 1:20

My dates are formatted as dates and the times formatted as times, just as you seem to have done, so you can just add each date and time together again as you have done, but forget the "Hours" in the formulae.

If you just want the result in hours format the cells as I have indicated above but without the ":mm".

Please Read to FAQ if you have not already done so. Dates and Times are so simple once you understand, and for adding or subtracting you don't need the Hours function in your formulae.

Good Luck!

Peter Moran

 

[SkipVought]

simply
[tt]
=(C2+D2)-(A2+B2)
[/tt]
If you read the FAQ, you would know that time and date BOTH have the same units: DAYS.

As Peter pointed out, to display DURATION, format the Date/Time value as [red][hh][/red]:mm

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jmk418]

Thanks for the help guys. I did not know about the [] in the formatting section. Makes doing these calculations much simpler.
Thanks again.
Jeremy