Feb 14 2022 02:12 AM
Hi,
What settings do I use so that when I enter data on my workbook in automatically converts it to a serial number in formatting?
Thanks.
Kind regards
Siemon
Feb 14 2022 07:15 AM
It depends in part on how the Serial Numbers are formatted. If they take the form of nothing but numbers, 10 digits or whatever, you can just make sure the cells are formatted as "text" using the Format menu that appears at the top of your screen and selecting text from the Dialog box. Or from the tool bar (second image)
If it's more complicated, you'll need to use "Custom" and that enable you to create all kinds of things. Here's a good resource for that: https://exceljet.net/custom-number-formats
Feb 15 2022 07:05 AM - edited Feb 15 2022 07:07 AM
Feb 15 2022 08:48 AM
Solution
I originally thought you were talking of PRODUCT serial numbers, such as appear on the bottom of an appliance. Here are the words of your original post: What settings do I use so that when I enter data on my workbook in automatically converts it to a serial number in formatting?
Now it appears that what you're really asking about is converting a date to the standard Excel serial number for dates.
And the answer depends a LOT on how exactly you enter that date data (the year, month, and day): is each of those a separate cell? Are you entering it (or downloading it) such that it is text?
I've attached a spreadsheet with two possible approaches. One uses the DATE function, the other DATEVALUE....
There are lots of functions in Excel that work with dates. Here's a starting source of info...https://exceljet.net/excel-functions/excel-date-function
Feb 17 2022 04:45 AM
Feb 15 2022 08:48 AM
Solution
I originally thought you were talking of PRODUCT serial numbers, such as appear on the bottom of an appliance. Here are the words of your original post: What settings do I use so that when I enter data on my workbook in automatically converts it to a serial number in formatting?
Now it appears that what you're really asking about is converting a date to the standard Excel serial number for dates.
And the answer depends a LOT on how exactly you enter that date data (the year, month, and day): is each of those a separate cell? Are you entering it (or downloading it) such that it is text?
I've attached a spreadsheet with two possible approaches. One uses the DATE function, the other DATEVALUE....
There are lots of functions in Excel that work with dates. Here's a starting source of info...https://exceljet.net/excel-functions/excel-date-function