Date function problem

[jellenberger]

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

 

[VBA Geek]

have you tried

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

 

[jellenberger]

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

 

[jellenberger]

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!

 

[barry houdini]

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)

 

[jellenberger]

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!