Menu
What I want to do is so you have a list of random dates, it will never be the same so it has to be a universal formula, but you get any date, any date of the year and then every Thursday is the Starting of a new week. For each date you set the date equal to the Thursday that it is greater than the previous and less than the Thursday.
Example to clarify since I am being vague, say you get June 24th, 2012. June 24th is greater than June 21st, 2012 (which is the previous Thursday) and it is less than June 28th, 2012, which is the next Thursday. This means it should generate the value June 28th, 2012 in the column next to it.
Is that making any sense? Based on any date value you get, you calculate which Thursday date it should generate? Any way to do this in excel?
228
=date + 7 – WEEKDAY(date + 7 –dow) Where; Date- this shows the date that excel is expected to start counting from. Here, we use a cell range. Dow- this is simply “day of week”, shortened as dow.
Go to the Number tab in the Format Cells dialog box. Select Custom as the Category. Add dddd into the Type field for the full weekday name or ddd for the abbreviated weekday name. Press the OK button.
=WORKDAY(date,1,holidays) Taking into account weekends and holidays, the Excel WORKDAY Formula will automatically add days to our specified date.
This formula will give you the Thursday following a date in A1
=A1+7-WEEKDAY(A1+2)
If A1 is a Thursday it returns that date, if it should be A1+7 if A1 is Thursday then change to this version
=A1+8-WEEKDAY(A1+3)
177
=IF(WEEKDAY(A1) < 5, A1+5-WEEKDAY(A1), A1+5+7-WEEKDAY(A1))
Will give you the next Thursday from the date given in Cell A1 in Excel. If the input date is a Thursday, it will give the next Thursday.
Use <= in the conditional if you want to display the input Thursday when given a Thursday, rather than displaying the next Thursday.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
