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
Combining columns text and numbers
-
- Posts: 3
- Joined: Fri May 17, 2019 11:53 pm
Combining columns text and numbers
Open Office 4.1.6 on Windows 10
Re: Combining columns text and numbers
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Combining columns text and numbers
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 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.
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
=A1+B1
If they are text, then it's going to be a lot harder.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
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.
-
- Posts: 3
- Joined: Fri May 17, 2019 11:53 pm
Re: Combining columns text and numbers
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!
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
Re: Combining columns text and numbers
Use /View /Value hightlighting to determine whether your values are numbers or text.obviously numbers
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
-
- Posts: 3
- Joined: Fri May 17, 2019 11:53 pm
Re: Combining columns text and numbers
They are, highlights both my date and time columnRoryOF wrote:Use /View /Value hightlighting to determine whether your values are numbers or text.obviously numbers
Open Office 4.1.6 on Windows 10
Re: Combining columns text and numbers
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.
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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Combining columns text and numbers
Hi, and welcome to the forum.
=TEXT(date;"DDMMMYY")&" "&TEXT(time;"0000")
If you need to do calculations with the date-time, read on.
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.
If this combination just for display, not for calculation, just contatenate them.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.
=TEXT(date;"DDMMMYY")&" "&TEXT(time;"0000")
If you need to do calculations with the date-time, read on.
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.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!
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.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).