| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| New date based on one date field minus minutes in another field | Excel Discussion (Misc queries) | |||
| Linked date field in worksheet defaults a blank field as 1/0/1900 | Excel Worksheet Functions | |||
| Converting a date field into a month-year only field | Excel Discussion (Misc queries) | |||
| Changing a text field to a date field | New Users to Excel | |||
| How to Join/concatenate a date field with a time field in Excel? | Excel Discussion (Misc queries) | |||
![Janet Kreinbrink[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
