Formula to calculate days from Monday

[astaticpunk]

Hi,

I am new to this site and am trying to determine a formula that can output how many days from Monday I am.

I have a product that expires 273 days from the monday of the current week.

Example todays product would expire 273 days from 4/1 (Monday). Tomorrow's would expire 273 days from 4/1 also.

What I am in need of is a calculation that can look at todays date and know that it was one day ago that it was monday and calculate 39 weeks from yesterday. Tomorrows would know to calculate back 2 days so that the expiration date from Monday - Sunday is all expiring 273 days from that weeks monday

My current calcuation takes 273+todays date (insert calculation that determines wed is three days)

Any help would be appreciated

Thank You!

[benishiryo]

hi astaticpunk, welcome to the forum. use WEEKDAY to return the day of the week.
=WEEKDAY(TODAY(),2)+1

the "2" behind returns 1 for Mon, 7 for Sunday. so if today is Wed, it takes today's date minus 3. that will give you Sunday of last week. so add plus 1 to it

[FDibbins]

Hi and welcome to the forum

weekday(today(),2) will give you today (wednesday 3 April) as 3, so you could use something like =weekday(today(),2)-(weekday(today(),2)-1) to give you monday (1) and then add your days to that?

[astaticpunk]

Hi,

Thanks for the information. How can I have Monday = 0, Tues =1, Wed = 2, Thu=3, Fri =4, Sat =5, Sun=6 ?

[astaticpunk]

What would it be with Mon=0, Tues = 1, etc. Monday doesn't need any values added to it so it should always have 0 and add the days to it

[FDibbins]

change the ,2 to ,3

[astaticpunk]

Thats perfect. Thank you!

I don't suppose you know how I could write this into a programing type language. It's for a piece of labeling software. When input as is, it returns a syntax error

[FDibbins]

sorry, my VBA sux

[astaticpunk]

For some reason it's only outputting 1 regardless of what date it is

=weekday(today(),3)-(weekday(today(),3)-1)

Even if I change my clock to say thurs it should show 3