Here's the curious thing though: =TEXT(B3,"dd/mm/yyyy")
means that you have a value in B3 that Excel considers a NUMBER.. --------------------------- TEXT(value, format_text) The TEXT function syntax has the following arguments: - value Required. A numeric value, a formula that evaluates to a numeric value, or a reference to a cell containing a numeric value. --------------------------- and you want to convert the NUMBER to TEXT in the format of: "dd/mm/yyyy" and... =TEXT(C3,"hh:mm") means that in C3 you have a NUMBER that is a decimal (as in: a fractional part of a day. .25 is 1/4 of a 24 hour day, so it would be 6:00am) By definition, the first parameter of the TEXT() function is a NUMBER. If it is NOT numeric, then the TEXT() function just passes the original value. For example: If the value in B3 is "11/27/2012 " (with a trailing space) Then the TEXT() function will drop the space and convert it to a number (41240) before evaluating the TEXT() function. But IF the number has a PRECEDING space: " 11/27/2012" Then =TEXT(B3,"mm/dd/yyyy") evaluates to: " 11/27/2012" to check, change the TEXT() function to: =TEXT(B3,"dd-mmm-yyyy" If the B3 is a number, then the result should be "27-Nov-2012" If it is NOT a number, then the result should look like " 11/27/2012". I guess what I'm saying is: If TEXT() is actually working correctly, then the two values (B3 and C3) are numeric. If they're numeric, then =B3+C3 will work also. Because when all is said and done, you really want the end result to be a number DISPLAYED as a Date/Time in Military format. Otherwise, if you choose to sort, or perform calculations: (number of hours between two values) this must be a number. Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: JenIT <jen...@alliedstaffing.com> To: excel-macros@googlegroups.com Sent: Thu, November 29, 2012 2:21:50 PM Subject: Re: $$Excel-Macros$$ convert time formatted as text to military time and join with date field thanks - I was so over-thinking this. Such and simple and straight foward solution. THANK YOU!!! On Thursday, November 29, 2012 12:01:43 PM UTC-6, Excel Beginner wrote: Hi Jenny, > > > Use this =TEXT(B3,"dd/mm/yyyy")&" "&TEXT(C3,"hh:mm") > > > > > >-- >Regards, > > >Excel Beginner > > >On Thu, Nov 29, 2012 at 11:20 PM, JenIT <jen...@alliedstaffing.com> wrote: > >My data is opened from a downloaded file. I do not have an import functions >as data exports as excel and I believe my cells are formatted as Text since I >cannot seem to make any formatting change the data. I need to to change >from >it's current format to military time and then also combine the new military >time >filed with the date filed in front of it. Any help would be greatly >appreciated! >> >>Date In Time Need the cell to look like this >> >>11/27/2012 02:00 PM 11/27/2012 14:00 >>11/27/2012 03:36 PM 11/27/2012 15:36 -- >>Join official Facebook page of this forum @ https://www.facebook.com/ >>discussexcel >> >>FORUM RULES >> >>1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not >>get >>quick attention or may not be answered. >>2) Don't post a question in the thread of another member. >>3) Don't post questions regarding breaking or bypassing any security measure. >>4) Acknowledge the responses you receive, good or bad. >>5) Jobs posting is not allowed. >>6) Sharing copyrighted material and their links is not allowed. >> >>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>members >>are not responsible for any loss. >>--- >>You received this message because you are subscribed to the Google Groups "MS >>EXCEL AND VBA MACROS" group. >>To post to this group, send email to excel-...@googlegroups.com. >>To unsubscribe from this group, send email to excel-macros...@ >googlegroups.com. >>Visit this group at http://groups.google.com/group/excel-macros?hl=en. >> >> >> > > > > >-- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
