# Date function problem

#### jellenberger

##### New Member
Hello,

I use the below function to display the first day of the current week (I use this in a Jet Report). In the function D5 is =TEXT(C5,"dddd") and C5 is =TODAY().

=IF(D5="Monday",C5-1,IF(D5="Tuesday",C5-2,IF(D5="Wednesday",C5-3,IF(D5="Thursday",C5-4,IF(D5="Friday",C5-5,IF(D5="Saturday",C5-6,C5-7))))))

Does anyone know how to modify this to show the first day of the current month if the first day of the current week happens to land in the previous month? Example: If today's date C5 is 09/01/14 then the first day of the week is 08/31/14. I need it to be 09/01/14.

The reason is I report on sales by week, month and YDT. When the month ends i don't want to include that data in the first week of the month.

Thanks,
Jason

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
have you tried

Code:
``=MAX(TODAY()-WEEKDAY(TODAY(),1)+1,EOMONTH(TODAY(),-1)+1)``

No I haven't but I just added it to my report. THANKS!

VBA Geek, I now realize I have another date problem when the first day of the week is in the previous month. Example: on Tuesday September 3 the first day of the week will be August 31 but I would like it to be September 1. Do you have a function that will show the first day of the week unless the first day is in the previous month, then show the first day of the month?

Thanks!

Doesn't VBA Geek's suggestion already do that? If today is 3rd Sept 2014 the formula returns 1st Sept 2014 - you can test any date by changing the formula to reference a cell, e.g. put your date in A1, e.g. 3-sep-2014 and use this formula in B1

=MAX(A1-WEEKDAY(A1,1)+1,EOMONTH(A1,-1)+1)

Sorry, Barry you are correct. I have many Jet Report functions on this spreadsheet and it's making my head spin. This works great! Thanks again!

Replies
7
Views
258
Replies
4
Views
191
Replies
3
Views
125
Replies
4
Views
172
Replies
14
Views
445

1,203,460
Messages
6,055,556
Members
444,797
Latest member
18ecooley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back