General Formatting

[Siemonsmit]

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

[#2]

@Siemonsmit 

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

[#3]

Hi,

Thanks Mathetes.

I am trying to convert dates to serial numbers so the =DATE() formula will work. Its currently picking up a #VALUE error because the date cannot be converted to serial numbers. I have tried using the formatting tabs on the top ribbon with no success so I am thinking there must be a general setup format which allows it to convert the numbers. I looked online and nothing is working :\

[#4 · Accepted solution]

@Siemonsmit 

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

[#5]

@mathetes 

Thank you mathetes, Great help - Came right with all of my formulas.

Cheers

Siemon