How do you combine a date field and a time field into one?
 

I have a data file where column A = date (as a number value) and column B =
time (in "general" format). Example:

A B
422 1700
423 1350
424 800
425 649
.... ...

Where 422 = 2/13/09 (I know, it's a messed up data file).
and where 1700 = 17:00.

I used the following to change column A to the date:
=DATE(YEAR(TODAY()),1,VALUE(D14)-378)

I used the following to change column B to miliary time:
=TIME(E15/100,MOD(E15,100),0)

But when I concatenate the two result fields, I get: "39863
0.708333333333333 " when I want to get "2/13/2009 17:00."

Changing the date formatting on the field doesn't do anything.

Thanks.

How do you combine a date field and a time field into one?
 

Try this:
=TEXT(D1,"dddd")&" "&TEXT(E1,"hh:mm AM/PM")

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Janet Kreinbrink" wrote:

I have a data file where column A = date (as a number value) and column B =
time (in "general" format). Example:

A B
422 1700
423 1350
424 800
425 649
... ...

Where 422 = 2/13/09 (I know, it's a messed up data file).
and where 1700 = 17:00.

I used the following to change column A to the date:
=DATE(YEAR(TODAY()),1,VALUE(D14)-378)

I used the following to change column B to miliary time:
=TIME(E15/100,MOD(E15,100),0)

But when I concatenate the two result fields, I get: "39863
0.708333333333333 " when I want to get "2/13/2009 17:00."

Changing the date formatting on the field doesn't do anything.

Thanks.

How do you combine a date field and a time field into one?
 

Whoops, screwed that up. Try one of these:
=TEXT(D1,"mm/dd/yyyy")&" "&TEXT(E1,"hh:mm AM/PM")

=TEXT(D1,"mm/dd/yyyy")&" "&TEXT(E1,"hh:mm")

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Janet Kreinbrink" wrote:

I have a data file where column A = date (as a number value) and column B =
time (in "general" format). Example:

A B
422 1700
423 1350
424 800
425 649
... ...

Where 422 = 2/13/09 (I know, it's a messed up data file).
and where 1700 = 17:00.

I used the following to change column A to the date:
=DATE(YEAR(TODAY()),1,VALUE(D14)-378)

I used the following to change column B to miliary time:
=TIME(E15/100,MOD(E15,100),0)

But when I concatenate the two result fields, I get: "39863
0.708333333333333 " when I want to get "2/13/2009 17:00."

Changing the date formatting on the field doesn't do anything.

Thanks.

How do you combine a date field and a time field into one?
 

Not sure what all of your possible time formats look like, so using your
base formulas...

How about this:

=DATE(YEAR(NOW()),1,D14-378)+TIME(E15/100,MOD(E15,100),0)

Custom format as m/d/yyyy h:m

--
Biff
Microsoft Excel MVP


"Janet Kreinbrink" wrote in
message ...
I have a data file where column A = date (as a number value) and column B =
time (in "general" format). Example:

A B
422 1700
423 1350
424 800
425 649
... ...

Where 422 = 2/13/09 (I know, it's a messed up data file).
and where 1700 = 17:00.

I used the following to change column A to the date:
=DATE(YEAR(TODAY()),1,VALUE(D14)-378)

I used the following to change column B to miliary time:
=TIME(E15/100,MOD(E15,100),0)

But when I concatenate the two result fields, I get: "39863
0.708333333333333 " when I want to get "2/13/2009 17:00."

Changing the date formatting on the field doesn't do anything.

Thanks.

How do you combine a date field and a time field into one?
 

Not sure why the 422 equates to 2/13/09; but, given that it does, then this
formula appears to work...

=A1+TEXT(B1,"0\:00")+39435

--
Rick (MVP - Excel)


"Janet Kreinbrink" wrote in
message ...
I have a data file where column A = date (as a number value) and column B =
time (in "general" format). Example:

A B
422 1700
423 1350
424 800
425 649
... ...

Where 422 = 2/13/09 (I know, it's a messed up data file).
and where 1700 = 17:00.

I used the following to change column A to the date:
=DATE(YEAR(TODAY()),1,VALUE(D14)-378)

I used the following to change column B to miliary time:
=TIME(E15/100,MOD(E15,100),0)

But when I concatenate the two result fields, I get: "39863
0.708333333333333 " when I want to get "2/13/2009 17:00."

Changing the date formatting on the field doesn't do anything.

Thanks.

How do you combine a date field and a time field into one?
 

Hi Janet

Try the below formula instead...and format to date/time format....

=DATE(YEAR(TODAY()),1,VALUE(D14)-378)+
TIME(LEFT(TEXT(E14,"0000"),2),RIGHT(TEXT(E14,"0000 "),2),0)

If this post helps click Yes
---------------
Jacob Skaria


"Janet Kreinbrink" wrote:

I have a data file where column A = date (as a number value) and column B =
time (in "general" format). Example:

A B
422 1700
423 1350
424 800
425 649
... ...

Where 422 = 2/13/09 (I know, it's a messed up data file).
and where 1700 = 17:00.

I used the following to change column A to the date:
=DATE(YEAR(TODAY()),1,VALUE(D14)-378)

I used the following to change column B to miliary time:
=TIME(E15/100,MOD(E15,100),0)

But when I concatenate the two result fields, I get: "39863
0.708333333333333 " when I want to get "2/13/2009 17:00."

Changing the date formatting on the field doesn't do anything.

Thanks.

How do you combine a date field and a time field into one?
 

This worked perfectally! Thank you!

"Jacob Skaria" wrote:

Hi Janet

Try the below formula instead...and format to date/time format....

=DATE(YEAR(TODAY()),1,VALUE(D14)-378)+
TIME(LEFT(TEXT(E14,"0000"),2),RIGHT(TEXT(E14,"0000 "),2),0)

If this post helps click Yes
---------------
Jacob Skaria


"Janet Kreinbrink" wrote:

I have a data file where column A = date (as a number value) and column B =
time (in "general" format). Example:

A B
422 1700
423 1350
424 800
425 649
... ...

Where 422 = 2/13/09 (I know, it's a messed up data file).
and where 1700 = 17:00.

I used the following to change column A to the date:
=DATE(YEAR(TODAY()),1,VALUE(D14)-378)

I used the following to change column B to miliary time:
=TIME(E15/100,MOD(E15,100),0)

But when I concatenate the two result fields, I get: "39863
0.708333333333333 " when I want to get "2/13/2009 17:00."

Changing the date formatting on the field doesn't do anything.

Thanks.