Combining columns text and numbers

Discuss the spreadsheet application

Combining columns text and numbers

Postby coyotes_uk » Fri May 17, 2019 11:57 pm

Hi all

I need some help combining two columns with a mixture of text and numbers.
For example:

Column one has a date in it, format being 05Aug19 etc..
Column two has a time in it, 24hr clock style so, times like 0740 and 1730 etc....

I need to be able to combine the two to get 05Aug19 0740 etc...

Thanks
Mark
Open Office 4.1.6 on Windows 10
coyotes_uk
 
Posts: 3
Joined: Fri May 17, 2019 11:53 pm

Re: Combining columns text and numbers

Postby robleyd » Sat May 18, 2019 1:50 am

Are these actually dates and times/numbers, or simply text strings? The solution will be different. Check with Ctrl + F8 or View | Value Highlighting. Text cells are formatted in black, formulas in green, and number cells in blue, no matter how their display is formatted.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2865
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Combining columns text and numbers

Postby RusselB » Sat May 18, 2019 1:53 am

Welcome to the Forums
Is the date and time in their respective columns actually numbers that have been formatted to appear as numbers & text (as in the case of the date) or are they text?
If they are numbers, with the date being in column A and the time in column B, in column C enter
Code: Select all   Expand viewCollapse view
=A1+B1
then use the Cell formatting to display the date and time as you wish.
If they are text, then it's going to be a lot harder.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5285
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Combining columns text and numbers

Postby coyotes_uk » Sat May 18, 2019 9:01 am

Thanks for the responses, I should have been clearer.
So the date column, while shows 05Aug is actually 08/05/2019 and the time column is obviously numbers.
When I try =C3+D3 I get 15/04/2024 00:00 in the destination cell in the format I want so, not sure its worked!
Open Office 4.1.6 on Windows 10
coyotes_uk
 
Posts: 3
Joined: Fri May 17, 2019 11:53 pm

Re: Combining columns text and numbers

Postby RoryOF » Sat May 18, 2019 9:16 am

obviously numbers


Use /View /Value hightlighting to determine whether your values are numbers or text.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29260
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Combining columns text and numbers

Postby coyotes_uk » Sat May 18, 2019 9:23 am

RoryOF wrote:
obviously numbers


Use /View /Value hightlighting to determine whether your values are numbers or text.


They are, highlights both my date and time column
Open Office 4.1.6 on Windows 10
coyotes_uk
 
Posts: 3
Joined: Fri May 17, 2019 11:53 pm

Re: Combining columns text and numbers

Postby robleyd » Sat May 18, 2019 9:43 am

It would seem that your 'date' is actually stored as a date - which is really a number representing n days since 1899-12-30 (unless you have changed the default) but your time is stored as an integer, not a decimal part of a day.

Perhaps you might want to review sections 3 and 4 of [Tutorial] Ten concepts that every Calc user should know for more information on how Calc stores dates and times. Also search the Help - F1 - for date and time functions.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2865
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Combining columns text and numbers

Postby MrProgrammer » Sat May 18, 2019 3:40 pm

Hi, and welcome to the forum.

coyotes_uk wrote:Column one has a date in it …. Column two has a time in it, 24hr clock style so, times like 0740.
I need to be able to combine the two to get 05Aug19 0740.
If this combination just for display, not for calculation, just contatenate them.
=TEXT(date;"DDMMMYY")&" "&TEXT(time;"0000")
If you need to do calculations with the date-time, read on.

coyotes_uk wrote:So the date column, while shows 05Aug is actually 08/05/2019 and the time column is obviously numbers.
When I try =C3+D3 I get 15/04/2024 00:00 in the destination cell in the format I want so, not sure its worked!
Something is wrong with your dates. 15/04/2024 cannot be MM/DD/YYYY so it must be DD/MM/YYYY, which is what I'd expect someone with name coyotes_uk to use. However you say 05Aug is 08/05/2019 which would be format MM/DD/YYYY. I will assume you meant 05/08/2019. Because xx/yy/zzzz is not clear, I will now use international standard YYYY-MM-DD for dates.

Subtracting the date value for 2019-08-05 from the date value for 2024-04-15 gives 1715. You did not tell us your time. Is it 17:15? If so, you have a number in D3 but not a time value. Study sections 3. Dates in cells and section 4. Times in cells in Ten concepts that every Calc user should know. To convert number 1715 to time value 17:15 review [Tutorial] Text to Columns and especially Q11/A11. When a cell displays date 2019-08-05 its value is 43682 (assuming use of the OpenOffice default base date 1899-12-30); when a cell displays time 17:15 its value is .71875; when a cell displays date-time 2019-08-05 17:15 its value is 43682.71875.

If you need additional assistance attach a document demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). It is possible, but messy, to combine a date with a non-time integer value, however it is better to learn how to represent and use times in a spreadsheet in the standard way. I recommend you not use integers for times.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3779
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 21 guests