| Home |
| Search |
| Today's Posts |
|
#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. |
| 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)
Threaded Mode
