Can this even be done?

[Rich_Prince]

I’m looking for a timesaver here, but the more I think about it, the less I’m sure that this can actually be done. The sheet I’ve made has a row containing dates running along the top. However, we’re not just talking 01-Jan, 02-Jan, 03-Jan, etc. Here’s what a week looks like on my sheet:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Mon 06 Jun AM<o></o>
Mon 06 Jun PM<o></o>
Mon 06 Jun EVE<o></o>
Tue 07 Jun AM<o></o>
Tue 07 Jun PM<o></o>
Tue 07 Jun EVE<o></o>
Wed 08 Jun AM<o></o>
Wed 08 Jun PM<o></o>
Wed 08 Jun EVE<o></o>
Thu 09 Jun AM<o></o>
Thu 09 Jun PM<o></o>
Thu 09 Jun EVE<o></o>
Fri 10 Jun AM<o></o>
Fri 10 Jun PM<o></o>
Fri 10 Jun EVE<o></o>
Sat 11 Jun AM<o></o>
Sat 11 Jun PM<o></o>
Sun 12 Jun.<o></o>
<o></o>
I was hoping there’d be a way to format the cells so that I could autofill, but of course Excel will recognise AM/PM, but not “EVE”. The other problem is that an EVE cell is not required for a Saturday, and a Sunday doesn’t need an AM, PM or EVE. So, my question is: Is there any way I can extend the dates shown above, for a whole year, without typing them all in manually? I really can’t think of one, but I’m hoping there’s some sort of formula or code that I haven’t even considered.<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Rich. <o></o>

 

[GTO]

"Mon 06 Jun AM"

Is that all in one cell?

Regardless of that bit, are these the all inclusive rules?
"The other problem is that an EVE cell is not required for a Saturday, and a Sunday doesn’t need an AM, PM or EVE."

...and is a temp macro okay?

 

[Rich_Prince]

Yes to all three questions! Anything that means I don't have to sit here and type it all manually would be a life saver.

It was only ever meant to be a one-use sheet, but now the boss wants it extended for a whole year. I'd have designed it totally differently if I'd known that! So any help at all would be greatly appreciated.

 

[MikeWx]

Assuming the values you have shown start in Cell A1, enter the following formula in the first blank cell (A19) and copy it down as far as you need it:

=TEXT(DATEVALUE(MID(A1,5,6))+7,"ddd")&" "&TEXT(DATEVALUE(MID(A1,5,6))+7,"dd mmm")&RIGHT(A1,LEN(A1)-10)

 

[Peter_SSs]

Welcome to the MrExcel board!

I've also done my suggestion down the column rather than across row 1 as it is easier to show on the board, but you can just as easily do this across.

Put the first week (18 cells) in manually as you have done. Then try this formula copied down.

Excel Workbook
	A
1	Mon 06 Jun AM
2	Mon 06 Jun PM
3	Mon 06 Jun EVE
4	Tue 07 Jun AM
5	Tue 07 Jun PM
6	Tue 07 Jun EVE
7	Wed 08 Jun AM
8	Wed 08 Jun PM
9	Wed 08 Jun EVE
10	Thu 09 Jun AM
11	Thu 09 Jun PM
12	Thu 09 Jun EVE
13	Fri 10 Jun AM
14	Fri 10 Jun PM
15	Fri 10 Jun EVE
16	Sat 11 Jun AM
17	Sat 11 Jun PM
18	Sun 12 Jun
19	Mon 13 Jun AM
20	Mon 13 Jun PM
21	Mon 13 Jun EVE
22	Tue 14 Jun AM
23	Tue 14 Jun PM
24	Tue 14 Jun EVE
25	Wed 15 Jun AM
26	Wed 15 Jun PM
27	Wed 15 Jun EVE
28	Thu 16 Jun AM
29	Thu 16 Jun PM
30	Thu 16 Jun EVE
31	Fri 17 Jun AM
32	Fri 17 Jun PM
33	Fri 17 Jun EVE
34	Sat 18 Jun AM
35	Sat 18 Jun PM
36	Sun 19 Jun
37	Mon 20 Jun AM
Fill dates

 

[GTO]

I'm not very stellar with formulas, but that certainly seems easier. Just as its already typed out, here's what I came up with.

In a Standard Module:

Option Explicit
    
Sub AddFunkyDates()
Dim _
dtmStart            As Date, _
i                   As Long, _
ii                  As Long, _
n                   As Long, _
aryDateSet(1 To 3)  As Variant
    
    If TypeName(ActiveSheet) = "Worksheet" Then
        For i = 1 To 3
            aryDateSet(i) = Application.InputBox( _
                                "Starting " & Array("Year?  (Like: 2011)", _
                                                    "Month? (Like: 1,2,3...12)", _
                                                    "Day? (1 - 31)")(i - 1), _
                                "Fill in req'd data in numbers only", , , , , , 1)
            If aryDateSet(i) = 0 Then
                MsgBox "No Dice!"
                Exit Sub
            End If
        Next
        
        dtmStart = DateSerial(aryDateSet(1), aryDateSet(2), aryDateSet(3))
        For i = 1 To 365
            Select Case Weekday(dtmStart - 1 + i, vbMonday)
            Case 1 To 5
                For ii = 0 To 2
                    n = n + 1
                    ActiveCell.Offset(-1 + n).Value _
                        = WeekdayName(Weekday(dtmStart - 1 + i, vbMonday), True, vbMonday) & Chr(32) _
                        & Format(dtmStart - 1 + i, "dd mmm", vbMonday) & Chr(32) & Array("AM", "PM", "EVE")(ii)
                Next
            Case 6
                For ii = 0 To 1
                    n = n + 1
                    ActiveCell.Offset(-1 + n).Value _
                        = WeekdayName(Weekday(dtmStart - 1 + i, vbMonday), True, vbMonday) & Chr(32) _
                        & Format(dtmStart - 1 + i, "dd mmm", vbMonday) & Chr(32) & Array("AM", "PM", "EVE")(ii)
                Next
            Case 7
                n = n + 1
                ActiveCell.Offset(-1 + n).Value _
                    = WeekdayName(Weekday(dtmStart - 1 + i, vbMonday), True, vbMonday) & Chr(32) _
                    & Format(dtmStart - 1 + i, "dd mmm", vbMonday) & "."
            End Select
        Next
    End If
End Sub

 

[Rich_Prince]

I can't thank you guys enough - clearly it can be done after all! And I was pretty close to one of those solutions, but truth be told I would never have got there on my own.

I really appreciate your efforts, I'll certainly be recommending this board to friends and colleagues.

Thanks again, this has saved me hours.