Excel Help (again!)

PostExcel Help (again!) by **<]:^D** » Sat Sep 05, 2015 11:52 am

hello more Excel questions from a noob.

ive got a schedule of tasks that i want to appear each week labelled MON, WED, FRI (3 separate cells).

So the cell needs to recognise the date of the current or next monday/wed/fri, sorted by date, but display as MON/WED/FRI in the actual cell.

I'm sure it's possible but i cant quite work it out.

thanks for any help guys.

PostRe: Excel Help (again!) by **Lotus** » Sat Sep 05, 2015 3:37 pm

Not sure I fully understand what you're trying to achieve, but if you have a list of dates in each cell like:

05/09/2015
06/09/2015
07/09/2015
08/09/2015
09/09/2015
10/09/2015
11/09/2015
12/09/2015
13/09/2015
14/09/2015

and then use the formula =TEXT(<cell reference>, "dddd") that should calculate the day and display Saturday, Sunday, Monday, etc. Do you only want it to work for Mon/Wed/Fri? Will you have a list of dates, and if so will they be consecutive dates or just Mon/Wed/Fridays? Might be helpful if we have a better picture of what data you'll have and how it's going to be displayed.

PostRe: Excel Help (again!) by **Rightey** » Sun Sep 06, 2015 12:58 am

Here you go, you need to arrange your day and task in a little array at the top of the page for this to work...

Like so...

Code: Select all

Sub dateTest()

Dim currentDate As String
Dim dayOfWeek As String

currentDate = Date

dayOfWeek = Weekday(currentDate)



Dim Tasks(1 To 3, 1 To 2) As String
Dim i As Integer, j As Integer

For i = 1 To 3
    For j = 1 To 2
        Tasks(i, j) = Cells(i, j).Value
    Next j
Next i

If dayOfWeek = 2 Then
    Cells(1, 4).Value = Tasks(1, 1)
ElseIf dayOfWeek = 4 Then
    Cells(1, 4).Value = Tasks(2, 1)
ElseIf dayOfWeek = 6 Then
    Cells(1, 4).Value = Tasks(3, 1)
End If

End Sub

The code right now will make a task appear in cell D1 only if it is the current task of the day, to make future tasks appear change the = in the if loop to a <

PostRe: Excel Help (again!) by **Fsolovaki** » Tue Sep 08, 2015 8:40 am

I believe that the opportunity to be quite high.

PostRe: Excel Help (again!) by **Rightey** » Tue Sep 08, 2015 5:46 pm

So did our solutions work?

PostRe: Excel Help (again!) by **<]:^D** » Tue Sep 08, 2015 11:22 pm

sorry lads been very busy and haven't had the chance to try it out - rest assured i will and get back to you. thanks very much for your help!

Excel Help (again!)

Who is online

Affiliates