Format With Leading Zeros

Fun_Geek

New Member
Joined
Apr 3, 2002
Messages
7
The User will enter a 8 digit date into a cell WITHOUT "/" separators, i.e., 01152002, representing January 15, 2002.

I would like the date displayed the same (01152002). It should not require the user to lead entry with apost (').

Also, another requirement is to validate date code on entry. It must be a valid calendar date (not something like January 34, 2002).

TIA

Mike
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
okay, for the format set a custom format as follows (right click the cell and choose Format Cells, then on the number tab select custom and enter the following formula):

ddmmyyyy

secondly, for the validation, select the cells and choose Validation from the Data menu, and set date and put in a lowest possible and highest possible date that the use could enter, eg. 01/01/90 and 01/01/20.
 
Upvote 0
On 2002-04-13 15:06, Fun_Geek wrote:
The User will enter a 8 digit date into a cell WITHOUT "/" separators, i.e., 01152002, representing January 15, 2002.

I would like the date displayed the same (01152002). It should not require the user to lead entry with apost (').

Also, another requirement is to validate date code on entry. It must be a valid calendar date (not something like January 34, 2002).

TIA

Mike

Hi Fun_Greek and daleyman:
daleyman's method did not work for me -- this is what did work for me
if you have the text value 1152002, 01152002 in cell A1, then in cell A2 put

=TEXT(A1,"00-00-0000")+0

This will convert your 7-digit or 8-digit text entry to a valid date simulating the day, the month, and the year in the text string.
HTH!


_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line
This message was edited by Yogi Anand on 2003-01-19 17:39
 
Upvote 0
Daleyman,

You said, "okay, for the format set a custom format as follows (right click the cell and choose Format Cells, then on the number tab select custom and enter the following formula): ddmmyyyy"

This doesn't work because:

(1) The user will enter in the format MMDDYYYY (even formatting as mmddyyyy) didn't work.

(2) Entering 01152002 in your above example will display "01265054" in the cell and "01/26/5054" in the formula bar.

You also said, "secondly, for the validation, select the cells and choose Validation from the Data menu, and set date and put in a lowest possible and highest possible date that the use could enter, eg. 01/01/90 and 01/01/20."

This will not work since the user will not be entering the "/". Again in the orignial post, I indicated that the user will enter directly to the cell "01152002" - no special characters for formatting.

Mike
 
Upvote 0
okay, try this then, pasting it into the module relating to the worksheet you are on.


Dim daypart As Integer, monthpart As Integer, yearpart As Integer

Private Sub Worksheet_Change(ByVal Target As Range)
If IsDate(Target.Value) Then Exit Sub
If Len(Target.Value) < 7 Then Exit Sub
strg = Target.Value
If Len(strg) = 7 Then strg = "0" & strg

'validation

valid = True
If Len(strg) <> 8 Then valid = False
If IsNumeric(strg) = False Then valid = False
If valid = True Then
daypart = Abs(Left(strg, 2))
monthpart = Abs(Mid(strg, 3, 2))
yearpart = Abs(Right(strg, 4))
If daypart < 1 Or daypart > 31 Then valid = False
If monthpart < 1 Or monthpart > 12 Then valid = False
If yearpart < 1900 Or yearpart > 2050 Then valid = False
End If

'result
If valid = True Then
Target.NumberFormat = "DDMMYYYY"
Target.Value = DateSerial(yearpart, monthpart, daypart)
Else
Target.Value = "ERROR"
End If
End Sub


...only problem is it does it for the whole sheet, if you want it to cover only one column, for example column B, insert after the first line of code...

If Target.Column <> 2 Then Exit Sub

...good luck this time!
 
Upvote 0
Hi
Assumes numbers are entered into column A
I may be the King of overkill, but it works..
Edit to suit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Len(Target) = 0 Then Exit Sub
Application.EnableEvents = False
If Len(Target) = 7 Then Target = "'0" & Target
If Not IsDate(Strings.Left(Target, 2) & "/" & _
Strings.Mid(Target, 3, 2) & "/" & Strings.Right(Target, 4)) Then
MsgBox "Invalid Date Entered"
Target.Select
Application.EnableEvents = True
Exit Sub
End If
Cells(Target.Row, Target.Column + 1).Value = _
Format(Strings.Left(Target, 2) & "/" & _
Strings.Mid(Target, 3, 2) & "/" & Strings.Right(Target, 4), _
"DDDD, MMMM DD, YYYY")
End If
Application.EnableEvents = True
End Sub

Tom
This message was edited by TsTom on 2002-04-13 20:20
 
Upvote 0
Sorry Daley,
I would not have even posted it if I would have seen your post...
Had to sign off right in the middle of writing it.
Tom
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top