Calculating age on Monday if today is Wednesday?

[servcoor]

Hi, all. I appreciate your time considering this:

Each day, I will be compiling a report showing the current age of activities we have in a workqueue, and then I need to show what the age of those activities will be come the next Monday.

Here's what I have so far...

Cell B1 contains =TODAY(), showing today's date, so that cell C1, which contains =TEXT(WEEKDAY(B1),"dddd") can show the day of the week. (Currently, Wednesday)

Cell C3 will contain the activity create (start) date. (Currently, 04/12/2011)

Cell H3 contains the age of the activity today, using =TODAY()-C3. (Currently, 29 days)

What formula can I enter in Cell I3 that will show the age of this activity come next Monday? I know that the activity will be 34 days old come Monday, or 5 days older, but I need a formula to calculate the age as of Monday as the week progresses.

Thanks for any help you can give!

 

[Gary McMaster]

Excel Workbook
	A	B	C
1	4/12/2011	5/11/2011	34.00
2
Sheet1

 

[servcoor]

Thanks so much for the help, Gary! That does work. However, I'm unclear about what the 3 signifies/does at the end of the formula in C1...

 

[Scott Huish]

From the help file:

WEEKDAY
Show All
Hide All
Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

Syntax

WEEKDAY(serial_number,return_type)

Serial_number is a sequential number that represents the date of the day you are trying to find. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type is a number that determines the type of return value.

Return_type Number returned
1 or omitted Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2 Numbers 1 (Monday) through 7 (Sunday).
3 Numbers 0 (Monday) through 6 (Sunday).

 

[servcoor]

Thanks, Hotpepper, for that info.

It looks like this will work just fine!

 

[Gary McMaster]

You may have to wrap that in an "If" function to check for Mondays. I suspect that this coming Monday will return the following Monday (or an extra 7 days). Then again maybe that's what you want?

Gary

 

[servcoor]

Thanks, Gary, for the heads-up...I'll watch for that. What I need is that when I run the report on a Monday, activities show their current age as of that day, plus what their age would be the following Monday. The point being that we have activities that must not age beyond 30 days before being completed. Particularly, on Friday, I need to show any activities that would be over 30 days as of Monday so that they can be completed before close of business Friday. While I could compute that manually each Friday, since I run the report daily, I want a formula that will do it automatically. It sounds like this will, if I'm understanding it.