Excel formula help

708 Views | 6 Replies | Last: 4 yr ago by jks08
ChiveOn
How long do you want to ignore this user?
AG
Greetings fellow young people.

I have a list of dates in excel that shows the current month's weekdays. I would like the list to auto-update based on month I list as the header of the list. is there a formula to do that?

i'm thinking of a EOMONTH and WORKDAY combo nested inside an IFS statement but not sure how to structure. basically I want it to show the workdays of the current month in the header and if it goes beyond that the cells are blank.

thoughts?
ChiveOn
How long do you want to ignore this user?
AG
For reference, I think the formula is something involving this similar one I did for MTD totals.

=SUMIFS(sheet2!S3:S264,sheet2!P3:P264,">="&(EOMONTH('sheet1'!$D$32,-1)+1),sheet2!P3:P264, "<="&EOMONTH('sheet1'!$D$32,0))
diehard03
How long do you want to ignore this user?
This is one of those problems where I would choose an inelegant solution quickly over a time-consuming elegant one.

I'd just vlookup from a 2 row table that tells me which days are weekends.

Or I would hardcode everything and hide the months im not on, rather than having an auto-update based on value in top.
ChiveOn
How long do you want to ignore this user?
AG
I did consider hiding the rows of non-current months. The formula is set to display data on a carefully crafted report (not my design) template that I need to stick to
jks08
How long do you want to ignore this user?
AG
So for January:
1/1
1/2
1/3
1/6
1/7

And so on?
Ulrich
How long do you want to ignore this user?
I'm not totally clear on what you want and I'm not at a computer but:

If you input 1/1/2020 in C2, then have a column numbered from 0 to about 25 starting in B3, you can use WORKDAY($C$2,B3) and drag it down. If you want to exclude holidays, there can be a third term in WORKDAY where you refer to a range holding the holidays.

To hide the days after the next month, you could do an IF statement or you could use conditional formatting.

For the formula, you would basically need the WORKDAY formula above twice. You can either have it in a separate column or do this: =IF( MONTH( WORKDAY($C$2,B3) ) = MONTH($C$2), WORKDAY($C$2,B3), "" )

For conditional formatting, highlight your range including all columns you want to disappear. Open the conditional formatting thing and choose "formula" at the bottom. If your WORKDAY formula is in column D and the range begins in row 3, enter this: =MONTH($D3)=MONTH($C$2). Then set a custom number format of three semicolons, like so: ;;;
Caveman96
How long do you want to ignore this user?
AG
It's a lot easier to do this with two VBA routines -- one that calculates and enters the dates, and another that triggers when the date changes.

Assuming a Worksheet named "Sheet1":

Enter both these subs in Sheet1's VBA. With the month/year in cell A1, this will enter the dates that are weekdays in column A beginning at A2:
Quote:

Private Sub ShowWeekdays()
Sheets("Sheet1").Range("A2:A" & Range("A2").End(xlDown).Row).ClearContents

Dim p As Integer
p = 2

Dim d As Date
d = Sheets("Sheet1").Range("A1").Value

Dim m As Variant
Dim y As Variant
m = Month(d)
y = Year(d)

Dim i, j As Long
i = Day(DateSerial(Year(d), Month(d) + 1, 1) - 1)
j = 1

Dim x As Date
Dim r As Range

For j = 1 To i
x = DateValue(m & "/" & j & "/" & y)
If Weekday(x, vbSunday) > 1 And Weekday(x, vbSunday) < 7 Then
Sheets("Sheet1").Cells(p, 1).Value = x
p = p + 1
Else
End If
Next j
End Sub
And this will run the above code automatically when A1's value changes.
Quote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Application.EnableEvents = False
Call ShowWeekdays
Application.EnableEvents = True
End If
End Sub
jks08
How long do you want to ignore this user?
AG
Similar to Ulrich's approach, without the conditional formatting and additional column:

Dropdown list in B1 with the first day of the month listed. You can format the cell to list month and year only.

In cell B2 enter this formula and drag down:
=IF(WORKDAY($B$1,ROW()-2)>EOMONTH($B$1,0),"",WORKDAY($B$1,ROW()-2))
Refresh
Page 1 of 1
 
×
subscribe Verify your student status
See Subscription Benefits
Trial only available to users who have never subscribed or participated in a previous trial.