• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Give Dates in Format

Abhijeet

Active Member
Hi

I have this macro Last date given from each cell data this macro works only if "-" this value then but i have , space then this not work. i Want if Year not mention then Today date i.e month is greater than today then give previous year. Please tell me how to do this
Code:
Sub Demo()
    With Sheet1.Cells(1).CurrentRegion.Rows
        ReDim ED(2 To .Count, 0)

        For R& = 2 To .Count
                          SP = Split(.Cells(R, 1).Value, "-")
                If IsDate(SP(UBound(SP))) Then ED(R, 0) = CDate(SP(UBound(SP))) Else _
            If IsDate(.Cells(R, 1).Value) Then ED(R, 0) = CDate(.Cells(R, 1).Value)
        Next
       
        With .Cells(2, 2).Resize(.Count - 1)
            .NumberFormat = "[$-409]dd/mmm/yy ":  .Value = ED
        End With
    End With
End Sub
 

Attachments

  • Dates.xlsm
    16.3 KB · Views: 1
Hi,

Can you explain the below

"i Want if Year not mention then Today date i.e month is greater than today then give previous year. Please tell me how to do this"
 
@Asheesh

From previous talks, it means that if the extracted date information is just a month and a day, assume it's the current year (e.g. 2015), unless that would make it a date in the future, then it is the previous year (e.g. 2014).

I tried to tackle this, but there's too many convoluted ways of the date appearing and getting interpreted for me. Curious as to how your data became so messed up! :p
 
Hi
If u have problem in th or nd to split if rest of things u have solution then please tell me (current macro only split "-" ) my data is contain "," then space if u find solution for this please tell me
 
Here's what I was able to get to. Can't read the last 2 cells in sample. Also misinterprets "19/11/14" as Nov 14, 2019 instead of Nov 19, 2014.
Code:
Sub MessyDates()
Dim rngInput As Range
Dim c As Range
Dim strDateBit As String
Dim dtNew As Date
Dim txtSplit As Variant

'==CHANGE AS NEEDED==
'How many columns to offset the output?
Const colOffset As Long = 2
'Where is the range of dates to read?
Set rngInput = Range("A2:A13")
'====================

Application.ScreenUpdating = False
For Each c In rngInput
    'Reset variable
    dtNew = 0
   
    'Remove all the extra stuff
    txtSplit = Split(c.Value, "-")
    txtSplit = Split(txtSplit(UBound(txtSplit)), ",")
    txtSplit = Split(txtSplit(UBound(txtSplit)), "  ")
    strDateBit = Trim(txtSplit(UBound(txtSplit)))
   
    'Try to convert date
    On Error Resume Next
    dtNew = DateValue(strDateBit)
    On Error GoTo 0
   
    'Check if able to convert
    If dtNew > 0 Then
        If dtNew > Date Then
            dtNew = DateSerial(Year(Date) - 1, Month(dtNew), Day(dtNew))
        End If
        c.Offset(, colOffset).Value = dtNew
    Else
        c.Offset(, colOffset).Value = "Unable to read"
    End If

Next c
Application.ScreenUpdating = True
End Sub
 
Hi Luke M

Thanks for macro but this type of dates 10-11-2014 macro give result Unable to read i am not understood this pls help me
 
Because we had previously stated that dash was only used to separate dates like in A2:
9-10 Nov
Macro can handle slash divisions, and space divisions.

How did your data get so messed up??
 
Ok can we do something what ever macro gives unable to read that data my 1st macro if can do from some of the correct i tried with put filter on unable to read but that macro result not give correct pls tell me how to do this
 

cpne9jy3.gif
Hi !

Did you finish to solve this very easy logic ?
'Cause kids around 10 years old found the way in about … 10 sec ‼
For days and months only, they said for years your sample is erratic,
no respect of your own rule !

If not, I need first the link from the source thread where you grab the code …
Or just ask to some children !​
 
Last edited:
cpne9jy3.gif
Hi !

Did you finish to solve this very easy logic ?
'Cause kids around 10 years old found the way in about … 10 sec ‼
For days and months only, they said for years your sample is erratic,
no respect of your own rule !

If not, I need first the link from the source thread where you grab the code …
Or just ask to some children !​
I have post my macro that macro gives me this output Luke M give me next macro in that this 10-11-2014 is not given that's why i ask to luke but Marc L can u please give me Tips how to do this with my all dates
 

As usual you don't respect forum rules
with a clear and complete understandable initial post
and an attached workbook without any error in expected result (!)
(if you had respected this, you would have a solution since a week ‼) :
you must now follow my rules, you're on my hand

So well read my previous post and just do what I expect !

When I explained to kids the purpose (waiting me to play outside with us),
one said « If I were the boss, I would take a hammer and break some
fingers of the fool who made these entries ! » (column A)
Kids seem cruel but often they are not wrong !

As they understood the purpose, the two boys and a girl looked at each other
and after a very short time (10 sec or less) they stared at me and started
to loudly laugh ! « Too easy » they claimed !
As they don't know Excel, they just asked only one question
how Excel recognizes dates …
When I explained your rule for year, they said « Don't waste your time
and come to play with us ! Expected result data don't respect this rule ! »
As I already knew that, I came out to play with them …

Outside the llttle girl said :
« You was kidding ? It's not an adult who asked this ! »
« Yes, it is ! » I answered. She looked surprised : « How can it be possible ?! »
And a boy launched : « Deficient brain I told you ! ». They started to run
and I could not resist to a great laugh …

Later, their parents asked me why I was laughing so much.
As I know they're not Excel fans, I told them « Just a kid story ! » …
 
Last edited:

Hi Abhijeet !

I wasn't joking, kids logic rocks !

So what did you not understand in post #10 ?
 

For reference, this post is post #15 …

Did you ever read post #10 ?
What did you not understand in ? I asked only one thing …
 
Back
Top