Adding Suffix to a date variable

**malczyk** · 07-03-2015, 04:51 AM

Hello, i've recently learned VBA and really enjoying it. I've managed to automate a large proportion of my job with considerably more accuracy than when i was doing it manually, my manager doesn't know yet though so we'll keep that on the down low for a while. Anyway i've basically got the core part of my spreadsheet working well but am having trouble with adding suffix's (st, nd, rd, th) to a date function. The code i've put together can be seen below. Basically when i add a new sheet every week it copies the old one and corrects all the equations, which works fine, and then it adds week commencing and that Monday's date to a cell at the top, which also works fine. But no matter what I try I only get 'st' after the day number and this is the part I have the issue with. Can anyone have a go at correcting my code? Also, if you could explain why my code was going wrong that would be really helpful - always aiming to learn from my mistakes.

Dim dteMonday As Date
    dteMonday = IIf(Weekday(Date) = 1, Date - 6, _
    Date - (Weekday(Date) - 2))
    
    If Day(dteMonday) = 1 Or 21 Or 31 Then
        n = "st"
    ElseIf Day(dteMonday) = 2 Or 22 Then
        n = "nd"
    ElseIf Day(dteMonday) = 3 Or 23 Then
        n = "rd"
    Else
        n = "th"
    End If
    
    Cells(1, 7).Value = "Week Commencing " & Format(dteMonday, "mmmm d") & n

Thanks Andrew

**jindon** · 07-03-2015, 05:41 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

try one of the folloiwngs

    Dim dteMonday As Date
    dteMonday = IIf(Weekday(Date) = 1, Date - 6, _
    Date - (Weekday(Date) - 2))
    Select Case Day(dteMonday)
        Case 1, 21, 31: n = "st"
        Case Is = 2, 22: n = "nd"
        Case 3, 23: n = "rd"
        Case Else: n = "th"
    End Select
    
    Cells(1, 7).Value = "Week Commencing " & Format(dteMonday, "mmmm d""" & n)

Dim dteMonday As Date
dteMonday = IIf(Weekday(Date) = 1, Date - 6, _
Date - (Weekday(Date) - 2))
n = Choose(Val(Right$(Day(dteMonday), 1)) + 1, "th", "st", "nd", "rd", "th", "th", "th", "th", "th", "th")
Cells(1, 7).Value = "Week Commencing " & Format(dteMonday, "mmmm d""" & n)

**malczyk** · 07-03-2015, 06:30 AM

Sorry about the code box I was unaware.

The top one worked perfectly thanks.

**jindon** · 07-03-2015, 06:34 AM

You are welcome and thanks for the code tag.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Adding Suffix to a date variable

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adding Suffix to a date variable

Re: Adding Suffix to a date variable

Re: Adding Suffix to a date variable

Re: Adding Suffix to a date variable

Thread Information

Users Browsing this Thread

Similar Threads

Adding an ascending suffix

Adding a suffix in series of numbers

[SOLVED] Adding Suffix to a cell value

[SOLVED] adding unique number suffix

Adding a suffix to a cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions