Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

excel formula

IF A1 =Today()

Is there a formula I can place in:-

B1 - To equal the very first Monday of the given month
C1 - The first day
0
Gazza83
Asked:
Gazza83
  • 2
2 Solutions
 
nutschCommented:
in C1
=DATE(YEAR(A1),MONTH(A1),1)

Thomas
0
 
zorvek (Kevin Jones)ConsultantCommented:
B1=A1-DAY(A1)+3-WEEKDAY(A1-DAY(A1)+1)+(1-(2>= WEEKDAY(A1-DAY(A1)+1))) * 7

Kevin
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
With C1 established by Thomas' formula, you can use in B1

=C1+MOD(8-WEEKDAY(C1,2),7)

or

=C1+IF(2 < WEEKDAY(C1), 7 - WEEKDAY(C1) + 2, 2 - WEEKDAY(C1))

cheers, teylyn
0
 
nutschCommented:
My take on B1
=MOD(9-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+DATE(YEAR(A1),MONTH(A1),1)

Thomas
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now