Converting Numbers to Date Format

[sinkintome812]

So for work, I have an excel spreadsheet that I use to keep track of documents I send out for specific patients I deal with. Some of the columns are dates that are always changing. I'm wondering if anyone knows a way to format the cells so that I don't have to type in the full date?

For example:

If I were to type in 42217, in the same cell it would automatically change it to 04/22/17.

 

[Rick Rothstein]

So for work, I have an excel spreadsheet that I use to keep track of documents I send out for specific patients I deal with. Some of the columns are dates that are always changing. I'm wondering if anyone knows a way to format the cells so that I don't have to type in the full date?

For example:

If I were to type in 42217, in the same cell it would automatically change it to 04/22/17.

You could use Cell Formatting to do it BUT that would only make the cell look like a date, but the value in the cell would still be the non-date number 42217. The only way to so what you want is to use VBA event code. Is that something you could use? If so, we would need to lock down what range of cells to apply the event code to.

 

[sinkintome812]

The VBA code is something I can do, I'm familiar with using macros. Now if I do use the Cell Formatting to do it, having the actual cell value being a non-date would I be able to sort the column by date?

 

[Greg Truby]

Now if I do use the Cell Formatting to do it, having the actual cell value being a non-date would I be able to sort the column by date?

That depends. Will you ever have more than one year? If so, then no. But the number format you would use would be something like:

00"/"00"/"00

You could always have a helper column that you use to sort on that would have a formula like:

=DATEVALUE(TEXT(A1,"00""/""00""/""00"))

Having said that, be aware that if you go for this option you will ALWAYS need to enter the day as two digits, you cannot shortcut november 1st as 11117 since this solution will interpret that to be january 11th.