Change Long Date to Short

[zoom38]

Hello, I am having difficulty working with dates. I have two dates, a start date in C11 and an end date in C13. My issue is that I need to do a calculation with the dates and then I would like to show the dates in their respective cells as a long date. However after the dates are changed to long dates I can no longer work with the dates. I tried changing its format to short date with no success. I tried putting the dates in as strings, text and dates with no success. Any help would be appreciated.



Private Sub Worksheet_Change(ByVal target As Range)
Dim myrange As Range
Dim mybegindate As Date
Dim myenddate As Date

Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Unprotect

' On Error Resume Next

If Not Intersect(target, Range("$C$11,$C$13")) Is Nothing Then
If target.Address = "$C$11" Then
mybegindate = target.Value
myenddate = DateValue(Range("c13").Value)
End If

If target.Address = "$C$13" Then
myenddate = target.Value
'myenddate = DateValue(Range("c11").value)
LValue = Format((mybegindate), "mm/dd/yyyy")
End If
End If

' monthdiff = DateDiff("m", Range("c11").Value, Range("c13").Value)
' daydiff = DateDiff("d", Range("c11").Value, Range("c13").Value)
' yeardiff = DateDiff("y", Range("c11").Value, Range("c13").Value)


Range("$C$11").Value = Format(Range("$C$11"), "Long Date")
Range("$C$13").Value = Format(Range("$C$13"), "Long Date")

Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Unprotect

End Sub

[Paul_Hossler]

Range("$C$11").Value = Format(Range("$C$11"), "Long Date")
Range("$C$13").Value = Format(Range("$C$13"), "Long Date")

Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Unprotect



1. Format ( ) returns a formatted string. Try

Range("$C$11").NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"

Range("$C$11").NumberFormat = ""m/d/yyyy"

for Long and Short. Note it is not .Value =

2. I'd think that last .Unprotect was supposed to be a .Protect

[zoom38]

Hi Paul thanks for the reply. Changing to long date did work. The issue is once the format is changed to long date, I can't get it to change back to short date or to another usable format. I tested Range("$C$11").NumberFormat = "m/d/yyyy". It may actually change the format but when I try to extract the date value from that cell, I cant use it, the value is still in long format.

I am befuddled!!

[snb]

dependent of your windows settings


Sub M_snb()
MsgBox FormatDateTime(Date, vbLongDate)
MsgBox FormatDateTime(Date, vbShortDate)
MsgBox FormatDateTime(Date, 1)
MsgBox FormatDateTime(Date, 2)
End Sub

[Paul_Hossler]

Internally Dates are really just number of days including fractions since Jan 1 1900

The display is subject to formatting, etc.

You should be able to calculate with anything that is a data (aka Double) and not a String




Option Explicit
Sub DatesEtc()
With ActiveSheet


.Range("C10").Value = DateSerial(2015, 6, 1)
.Range("C11").Value = DateSerial(2015, 6, 1) + TimeSerial(15, 25, 33)
.Range("C14").Value = DateSerial(2015, 6, 1)
.Range("C14").NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
.Range("C15").Value = DateSerial(2015, 6, 1) + TimeSerial(15, 25, 33)
.Range("C15").NumberFormat = "m/dd/yyyy"


MsgBox .Range("C10").Value
MsgBox .Range("C10").Value2
MsgBox .Range("C10").Text

'add one week
MsgBox "Add 1 week " & .Range("C10").Value + 7
MsgBox "Add 1 week " & .Range("C10").Value2 + 7
'this doesn't work since .Text is a string
' MsgBox .Range("C10").Text + 7

'add one week and 6 hours (.25 days)
MsgBox "Add 1 week and 6 hours " & .Range("C11").Value + 7.25
MsgBox "Add 1 week and 6 hours " & .Range("C11").Value2 + 7.25
'this doesn't work since .Text is a string
MsgBox .Range("C11").Text
End With
End Sub

[zoom38]

Thank you Paul for looking into this. Turns out my issue was created by me. I have the dates in the cells as text which is what was creating my problems. So I spent yesterday devising a sub that would work with text. You might wonder why I would do this, simply put, to control what the user enters. Normally if you enter in a date as 5/55 excel defaults to 5/1/1955. I wanted to control it so that the user must enter in the month and day so that when 5/55 is entered, an error message would pop up and require a new entry. I might have done it the long/wrong way, but it works.

Thanks again for your time.
Gary

[Paul_Hossler]

something to look at





Option Explicit
Sub Test()
Dim v As Variant
Dim sDate As String
Dim bGood As Boolean
bGood = False


Do While Not bGood
sDate = Application.InputBox("Enter Date as 'm/d/y'", "Dates", vbNullString, , , , , 2)
If Len(sDate) = 0 Or sDate = "False" Then
bGood = True
MsgBox "Exiting"
Else

v = Split(sDate, "/")
If UBound(v) <> 2 Then
MsgBox "Wrong format - needs to be m/d/y"

ElseIf Not IsNumeric(v(0)) Then
MsgBox "Month needs to be numeric"
ElseIf v(0) < 1 Or v(0) > 12 Then
MsgBox "Month needs to be between 1 and 12"

ElseIf Not IsNumeric(v(1)) Then
MsgBox "Day needs to be numeric"
ElseIf v(1) < 1 Or v(1) > 31 Then
MsgBox "Day needs to be between 1 and 31" ' crude I know

ElseIf Not IsNumeric(v(2)) Then
MsgBox "Year needs to be numeric"
ElseIf v(2) < 1990 Or v(2) > 2015 Then
MsgBox "Year needs to be between 1990 and 2015"
ElseIf Not IsDate(sDate) Then
MsgBox "Still not a date"

Else
bGood = True
MsgBox "That's a good date"

End If


End If
Loop

End Sub

[zoom38]

Thanks Paul, nice simple code. However if I modify the code to take the input from a cell, because of the formatting I can't get it to work for all situations. For example: when cell input is 5/15 it will default to 5/15/2015 which is ok. But if the cell input is 2/29, it defaults to 2/1/2029. Your code is simple but requires the month/day/year to be entered. Is there an easy modification to your code that will take a cell entry that will accept the excel default (month/day is entered the year defaults to the current year) and if an invalid month/day is entered an error message pops up? I did it all using text entries, quite lengthy and I'm sure not efficient. Your sub looks much more efficient if it can be tweaked as above. If there isn't an easy tweak, its not that important I will use what I have.

Thanks
Gary

[Paul_Hossler]

you can steal any of this logic



Option Explicit
Sub drv()
Dim v As Variant
v = CheckDate

If IsError(v) Then
MsgBox "No Date"
Else
MsgBox v
End If
End Sub

Function CheckDate() As Variant
Dim v As Variant
Dim sDate As String
Dim bGood As Boolean
bGood = False


Do While Not bGood
sDate = Application.InputBox("Enter Date as 'm/d/y' or 'm/d' for current year", "Dates", vbNullString, , , , , 2)
If Len(sDate) = 0 Or sDate = "False" Then
bGood = True
CheckDate = CVErr(xlErrNum)
Else

v = Split(sDate, "/")

Select Case UBound(v)
Case 0
MsgBox "Wrong format - needs to be 'm/d/y' of 'm/d'"

Case 1
If Not IsNumeric(v(0)) Then
MsgBox "Month needs to be numeric"
ElseIf v(0) < 1 Or v(0) > 12 Then
MsgBox "Month needs to be between 1 and 12"

ElseIf Not IsNumeric(v(1)) Then
MsgBox "Day needs to be numeric"
ElseIf v(1) < 1 Or v(1) > 31 Then
MsgBox "Day needs to be between 1 and 31" ' crude I know

Else
CheckDate = DateSerial(Year(Now), v(0), v(1))
bGood = True
End If

Case 2
If Not IsNumeric(v(0)) Then
MsgBox "Month needs to be numeric"
ElseIf v(0) < 1 Or v(0) > 12 Then
MsgBox "Month needs to be between 1 and 12"

ElseIf Not IsNumeric(v(1)) Then
MsgBox "Day needs to be numeric"
ElseIf v(1) < 1 Or v(1) > 31 Then
MsgBox "Day needs to be between 1 and 31" ' crude I know

ElseIf Not IsNumeric(v(2)) Then
MsgBox "Year needs to be numeric"
ElseIf v(2) < 1990 Or v(2) > 2015 Then
MsgBox "Year needs to be between 1990 and 2015"
Else
CheckDate = DateSerial(v(2), v(0), v(1))
bGood = True
End If

Case Else
MsgBox "Wrong format - needs to be 'm/d/y' of 'm/d'"
End Select
End If
Loop

End Function