Combining columns text and numbers

[coyotes_uk]

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

[robleyd]

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.

[RusselB]

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

=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.

[coyotes_uk]

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!

[RoryOF]

obviously numbers

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

[coyotes_uk]

obviously numbers

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

They are, highlights both my date and time column

[robleyd]

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.

[MrProgrammer]

Hi, and welcome to the forum.

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.

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.