• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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