Consulting

Results 1 to 19 of 19

Thread: Format date within formula in VBA

  1. #1
    VBAX Regular
    Joined
    May 2021
    Posts
    7
    Location

    Question Format date within formula in VBA

    Hello all. I'm brand new to this forum and a total VBA newbie so please let me know if I'm posting incorrectly and forgive my novice level of coding. I have attached (I think) a sample of data. Columns A thru N will be populated with a header line and varying amounts of lines of data, replaced each day as part of a larger Excel project. I am hoping to add a column to the right of the data which generates a unique identifier for each line of data. Basically, a formula that concatenates the date in column A, the symbol in column E, and a counter of the lines of data. The way I have tackled is by adding two columns, a "count" column and a "unique" column.

    Although I'm sure there's a MUCH better/cleaner way to do it. Column O, the "Count", seems to work. But I am having trouble with Column N, the "Unique Identifier" because of the date imbedded in the formula. If I leave as below, the date in P2 is being converted to a 5 digit number. I also tried Range("P2") = TEXT(A2,"mmddyyyy")&E2&O2 like I would if I were inserting directly into Excel cell but I get a compile error. Assuming it has something to do with number of quotation marks because it tells me "Expected End of Statement" at "mmddyyyy". Is there a way to imbed the date formatting within this formula?

    Here is what I have written:
    Sub AddCountandUniqueIdentifier()

    Sheets("Sheet1").Activate
    Dim LastRow As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    ' Adding a column that counts number of trades
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Count"
    Range("O2") = 1
    Range("O2:O" & LastRow).DataSeries , xlDataSeriesLinear

    ' Adding a column that creates unique identifier
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Unique"
    Range("P2") = "=A2&E2&O2"
    Range("P2:P" & LastRow).FillDown

    End Sub

    I am totally open to rewriting ALL of this or just addressing my date formatting issue. Thank you so much for any help and advice!
    Attached Files Attached Files

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Try this:
    Range("P2").Formula = "=Text($A2,"yyyymmdd"),&$E2&$O2"
    The reason for using yyyymmdd is that it sorts in chronological order

    Using mmddyyyy will sort by months(1 to 12) then by days, then by years.
    Jan 01
    Feb01
    .
    .
    Dec01
    Jan02
    Feb02
    .
    .
    .Dec02
    Jan03
    Feb03
    etc
    Or some other weird pattern

    What you are seeing when the result is numbers vice dates is Microsoft stores date as DateSerials, or the number of days counted since January 00, 1900. (Jan/1/1900 is DateSerial 1.) MS Applications Convert DateSerials to human readable Dates only for display to humans
    Last edited by SamT; 05-12-2021 at 12:46 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    May 2021
    Posts
    7
    Location
    Unfortunately I'm met with the same Compile error - Expected: end of statement on that line.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Try remove the comma after the closing parentheses.
    I expect the student to do their homework and find all the errrors I leeve in.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Behind the scenes Excel stores date and times as Doubles.

    Using Control+` to look at the real values and formulas

    Capture.JPG

    Some suggestions

    1. Always use Option Explicit at the top of the module

    2. No need usually to Select or Activate objects, like Worksheets, Ranges, etc. if you can address them directly

    3. Just using "Range(...)" by itself will refer to the ActiveSheet, which may not be what you want.
    Use something like Worksheets("Data").Range("A1")

    4. Your data was nicely arranged, so .CurrentRegion can be handy since it selects the block of cells

    5. To get a quote in a VBA string like for making a formula, you need to use two of them

    6. I added a hyphen between pieces of your Unique column

    Option Explicit
    
    
    Sub AddCountandUniqueIdentifier()
        Dim r As Range
        
        With Worksheets("Sheet1")
            .Range("O1").Value = "Count"
            .Range("P1").Value = "Unique"
        
            Set r = .Cells(1, 1).CurrentRegion
            
            .Range("O2").Value = 1
            .Range("O2").Resize(r.Rows.Count - 1, 1).DataSeries , xlDataSeriesLinear
            .Range("P2").Resize(r.Rows.Count - 1, 1).Formula = "=TEXT(A2,""yyyymmdd"")&""-""&E2&""-""&O2"
        End With
        
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Thanks, Paul. I rarely work with WorkSheet Formulas in VBA
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You overcomplicate things by using VBA to do this.
    You don't need column O.
    Avoid 'Option Explicit' in VBA; it's redundant.
    The formula is dependent of the international settings.
    The formula below is for the US setting.

    In P2, enter manually:

    PHP Code:
    =TEXT(A2,"yyyymmdd ")&E2&COUNTIF(E$2:E2,E2
    And fill P3:Pn

    If you'd insist to use VBA:

    Sub M_snb()
       With Sheet1.Cells(2, 16)
           .Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
           .AutoFill .Resize(200)
        End With
    End Sub

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by snb View Post

    1. You overcomplicate things by using VBA to do this.
    2. You don't need column O.
    3. Avoid 'Option Explicit' in VBA; it's redundant.
    4. The formula is dependent of the international settings. The formula below is for the US setting.
    5. In P2, enter manually:

    And fill P3:Pn


    6. If you'd insist to use VBA:

    Sub M_snb()
       With Sheet1.Cells(2, 16)
           .Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
           .AutoFill .Resize(200)
        End With
    End Sub

    1. Possibly, but it's neater and more flexible

    2. True, but 'helper' columns can avoid very long formulas and when you're learning can help show you what's happening

    3. Opinion. I disagree, especially when you're learning. The same for letting everything default to a Variant instead of a strongly Typed variable

    4. True, but Kentucky is part of the US and there's no indication that this has international use

    5. Manual effort that has to be done every time the amount of data changes

    6. Including COUNTIF( ..) in the formula could work, but .Resize(200) instead of using .CurrentRegion just adds formulas to empty rows.

    The OP can decide which way they prefer to go
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    No variables make 'Option explicit' the more redundant.
    It serves no purpose to repeat your opinions.
    Not bothering about variable types enhances the learning curve of anybody.
    The amount of readers is hopefully bigger than the TS and hopefully from more countries than only the US.
    To add to everybody's leaning curve: preferably use Excel's builtin options and avoid clumsy coding:

    Sub M_snb()
       With Sheet1.Cells(2, 16)
           .Parent.ListObjects.Add 1, .CurrentRegion
           .Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
        End With
    End Sub
    PS. It's no use commenting the suggestions put here by other helpers.
    Please restrict yourself to your own suggestions.
    I don't consider this forum as a debating forum.
    Last edited by snb; 05-13-2021 at 09:03 AM.

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by snb View Post
    No variables make 'Option explicit' the more redundant.
    It serves no purpose to repeat your opinions.
    Not bothering about variable types enhances the learning curve of anybody.
    The amount of readers is hopefully bigger than the TS and hopefully from more countries than only the US.
    To add to everybody's leaning curve: preferably use Excel's builtin options and avoid clumsy coding:

    PS. It's no use commenting the sugestions put here by other helpers.
    Please restrict yourself to your own suggestions.
    I don't consider this forum as a debating forum.
    What you call 'suggestions' are your opinions

    When I use 'Option Explicit' and you say 'Don't use Option Explicit' I could take that as a comment on my suggestion

    I agree this is not a debating forum, but not everyone has your years of experience so pontificating The Gospel of SNB without any explanations does not "enhance the learning curve of anybody"

    I have nothing more to say (and we can all agree that's a good thing )
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  11. #11
    VBAX Regular
    Joined
    May 2021
    Posts
    7
    Location
    Quote Originally Posted by SamT View Post
    Thanks, Paul. I rarely work with WorkSheet Formulas in VBA
    Thanks for the attempt SamT but I didn't have any success with your suggestions. Just for closure, I was able to get this to work: Range("P2") = "=TEXT(A2,""mmddyyyy"")&E2&O2"

  12. #12
    VBAX Regular
    Joined
    May 2021
    Posts
    7
    Location
    Quote Originally Posted by Paul_Hossler View Post
    .Range("P2").Resize(r.Rows.Count - 1, 1).Formula = "=TEXT(A2,""yyyymmdd"")&""-""&E2&""-""&O2"
    [/CODE]
    Paul_Hossler, your suggestions were INCREDIBLY helpful. Really, I learned so much by your notes and re-work on my codes. So that it is in my preferred format I rearranged the date format and removed the hyphens and get this code ("=TEXT(A2,""mmddyyyy"")&E2&O2") to work perfectly. Thank you so much!!

  13. #13
    VBAX Regular
    Joined
    May 2021
    Posts
    7
    Location
    Quote Originally Posted by snb View Post
    You overcomplicate things by using VBA to do this.
    You don't need column O.
    Avoid 'Option Explicit' in VBA; it's redundant.
    The formula is dependent of the international settings.
    The formula below is for the US setting.

    In P2, enter manually:

    PHP Code:
    =TEXT(A2,"yyyymmdd ")&E2&COUNTIF(E$2:E2,E2
    And fill P3:Pn

    If you'd insist to use VBA:

    Sub M_snb()
       With Sheet1.Cells(2, 16)
           .Value = "=Text(A2,""yyyymmdd "")&E2&countif($E$2:$E2,E2)"
           .AutoFill .Resize(200)
        End With
    End Sub
    I do need to use VBA and hope to avoid any manual fill as this is part of a much larger spreadsheet with indirect formulas, etc. Nevertheless, thank you for your suggestions. I love the thought of not having to create column O and instead imbedding a "count" within the "unique" formula. Unfortunately, I'm not able to get your countif formula to work as I hoped. I'd like a simple 1, 2, 3, 4, 5 count of however many rows of data there are. Instead, this formula generates a new count each time there is a repeated item in column E. I could just bee looking at it incorrectly. Could you kindly take a look at my updated spreadsheet and see if you agree?
    Attached Files Attached Files

  14. #14
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Like this? I took out the hyphens, but for scanability, I formatted the 'count' with leading 0's

    Although, it's not clear to me why you need it since just the row number would be a unique indentifier

    Capture.JPG


    Option Explicit
    
    
    Sub AddCountandUniqueIdentifier()
        Dim r As Range
        
        With Worksheets("Sheet1")
            .Range("O1").Value = "Unique"
        
            Set r = .Cells(1, 1).CurrentRegion
            
            .Range("O2").Resize(r.Rows.Count - 1, 1).Formula = "=TEXT(A2,""yyyymmdd"")&E2&TEXT(ROW()-1,""0000"")"
        End With
        
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  15. #15
    VBAX Regular
    Joined
    May 2021
    Posts
    7
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Like this? I took out the hyphens, but for scanability, I formatted the 'count' with leading 0's

    Although, it's not clear to me why you need it since just the row number would be a unique indentifier
    That worked perfectly. Thank you! I need a unique identifier bc I intend to reference it in a difference section of the project. For use in a vlookup or index match specifically. Does that make sense or am I going about it all wrong?

  16. #16
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by JenaB View Post
    bc I intend to reference it in a difference section of the project. For use in a vlookup or index match specifically. Does that make sense or am I going about it all wrong?
    Well, one difficulty I can see is that since your Unique ID is based on a formula, if you sort or delete the data, then the ROW part of the formula and therefore the Unique ID will change.

    That might not be what you want
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  17. #17
    VBAX Regular
    Joined
    May 2021
    Posts
    7
    Location
    That makes total sense. Thank you again to all of you who have helped me on my first forum post. I'll mark as solved at this time.

  18. #18
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    if you sort or delete the data, then the ROW part of the formula and therefore the Unique ID will change.
    You can make the Unique ID Static by changing
    .Range("P2").Resize(r.Rows.Count - 1, 1).Formula = "=TEXT(A2,""yyyymmdd"")&E2&O2"
    To
    For rw = 2 to r.Rows.Count
       If Cells(rw, "P") = "" Then _ 'If P is empty Then... Space&Undersore = Line continuation; No End If Required for single line If...Thens
       Cells(rw, "P") = Format(Cells(rw, "A"), "mmddyyyy") & Cells(rw, "E") & Cells(rw. '"O") 'Add Static Unique ID
    Next rw
    However, I still recommend using the international style Date Formatting "yyyymmdd". It prevents programmatic confusion about dates like 04052021 (Apr 05, 2021 or 04 May, 2021)
    Last edited by SamT; 05-13-2021 at 01:16 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  19. #19
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Some things to think about

    1. Instead of a unique identifier (which might cause problems) you could use a UDF (User Defined Function) to calculate values for reporting. Not as fast as using the intrinsic Excel worksheet functions, but something to consider

    2. Not clear what your end objective is, but Pivot tables can answer a lot of questions and have many formatting options

    Capture.jpg
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •