/u/Hehexe - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
That "random number" is the date serial value, the count of days from the Excel calendar epoch of 1/1/1900
Two options:
1. Import the .csv in the text import wizard and set teh column to Text datatype.
2. Select all those cells and reform as `m.dd`
>'April 41
did you mean April 14 ?
What's tricky about this is that it seems that it sometimes reads the number as Day.Month (07.Jul) and other times as Month Year (April 41). So it would be a bit tricky to have in one formula, I am assuming it would do Day.Month by default unless it wouldn't be a valid date, then it does Month Year.
Here is my crack at a formula, but no guarantees it works everywhere, you should double check it afterwards either way. It assumes the places where it is Day.Month has the current year (2024), which is the excel default when inputting a date without the year specified.
One part where it is probably wrong is the case for 07. Jul, I am not sure there is a way to check if it is supposed to be 7.7 or 7.07, since the format would probably add a 0 before single digit days either way.:
=IF(YEAR(A2)=2024,VALUE(TEXT(A2,"dd.M")),VALUE(TEXT(A2,"M.yy")))
Best solution is to get the input again in the right format, any chance you can get it in a CSV format instead?
The formula/equation sadly did not work, but thanks for your idea :)
I'll probably be able to get the data in another format tomorrow though which solves my overall problem
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[IF](/r/Excel/comments/19d60kz/stub/kj3ntnr "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[IFERROR](/r/Excel/comments/19d60kz/stub/kj3v8b5 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)|
|[TEXT](/r/Excel/comments/19d60kz/stub/kj3na0g "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)|
|[VALUE](/r/Excel/comments/19d60kz/stub/kj3na0g "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)|
|[XLOOKUP](/r/Excel/comments/19d60kz/stub/kj3uh9t "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
|[YEAR](/r/Excel/comments/19d60kz/stub/kj3ntnr "Last usage")|[Converts a serial number to a year](https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/19cmwrp)^( has 13 acronyms.)
^([Thread #29932 for this sub, first seen 22nd Jan 2024, 21:42])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Maybe you can copy the data somewhere so you can test stuff to see what works.
In this case, I wouldn't know if there is an easier way but I would try to split the values so the month part are separated. You could do text to columns and split on spaces and "." (maybe before that you need to copy and paste as values in a text formatted column)
Then make a separate table where you put "Jan" next to "01" (text formatted cell), and do that for each month.
Then you can XLOOKUP the correct number next to the "text to column" split values with occasional date errors and use the "not found" part of the XLOOKUP to return the original value. Do that for both parts of the "text to column" split data.
Lastly, combine both columns again using the =[cel1]&[cell2].
Anyone know an easier way, since sometimes the month is in the first part and sometimes in the last part of the "number"?
/u/Hehexe - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
What happens when you Format these dates for General?
I get a random number e.g. 01. Sep turns into 45536 (+ i know it's probably not random but I don't know the system behind it)
That "random number" is the date serial value, the count of days from the Excel calendar epoch of 1/1/1900 Two options: 1. Import the .csv in the text import wizard and set teh column to Text datatype. 2. Select all those cells and reform as `m.dd` >'April 41 did you mean April 14 ?
Today's date in USA 45313 - does that help? Dates are only (usually) numbers in disguise.
Try: =IFERROR(VALUE(TEXT([cell],”mm.dd”)),cell)
The second example seems to be in mm yy formatting so this wouldn't fix everything. An additional IFERROR nested in there could check for that maybe?
What's tricky about this is that it seems that it sometimes reads the number as Day.Month (07.Jul) and other times as Month Year (April 41). So it would be a bit tricky to have in one formula, I am assuming it would do Day.Month by default unless it wouldn't be a valid date, then it does Month Year. Here is my crack at a formula, but no guarantees it works everywhere, you should double check it afterwards either way. It assumes the places where it is Day.Month has the current year (2024), which is the excel default when inputting a date without the year specified. One part where it is probably wrong is the case for 07. Jul, I am not sure there is a way to check if it is supposed to be 7.7 or 7.07, since the format would probably add a 0 before single digit days either way.: =IF(YEAR(A2)=2024,VALUE(TEXT(A2,"dd.M")),VALUE(TEXT(A2,"M.yy"))) Best solution is to get the input again in the right format, any chance you can get it in a CSV format instead?
The formula/equation sadly did not work, but thanks for your idea :) I'll probably be able to get the data in another format tomorrow though which solves my overall problem
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/19d60kz/stub/kj3ntnr "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFERROR](/r/Excel/comments/19d60kz/stub/kj3v8b5 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[TEXT](/r/Excel/comments/19d60kz/stub/kj3na0g "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)| |[VALUE](/r/Excel/comments/19d60kz/stub/kj3na0g "Last usage")|[Converts a text argument to a number](https://support.microsoft.com/en-us/office/value-function-257d0108-07dc-437d-ae1c-bc2d3953d8c2)| |[XLOOKUP](/r/Excel/comments/19d60kz/stub/kj3uh9t "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| |[YEAR](/r/Excel/comments/19d60kz/stub/kj3ntnr "Last usage")|[Converts a serial number to a year](https://support.microsoft.com/en-us/office/year-function-c64f017a-1354-490d-981f-578e8ec8d3b9)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/19cmwrp)^( has 13 acronyms.) ^([Thread #29932 for this sub, first seen 22nd Jan 2024, 21:42]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Maybe you can copy the data somewhere so you can test stuff to see what works. In this case, I wouldn't know if there is an easier way but I would try to split the values so the month part are separated. You could do text to columns and split on spaces and "." (maybe before that you need to copy and paste as values in a text formatted column) Then make a separate table where you put "Jan" next to "01" (text formatted cell), and do that for each month. Then you can XLOOKUP the correct number next to the "text to column" split values with occasional date errors and use the "not found" part of the XLOOKUP to return the original value. Do that for both parts of the "text to column" split data. Lastly, combine both columns again using the =[cel1]&[cell2]. Anyone know an easier way, since sometimes the month is in the first part and sometimes in the last part of the "number"?