|
Calculate DATE from Weekday and Week #
Hello - (XL2002 on XP)
I'm building a template to report daily sales and am looking for a way to calculate the DATE for each day of the current week. Here's what I have so far: cell J3: =NOW() (formatted to MM/DD/YY) K3: week # (calc'd using a really long formula, TOO long to write here) J9-J15: the days of the week K9-K15: this is where I want the dates for the current week to go So, as a real example, right the values should be: J3: 01/16/07 K3: 0703 J9: Sunday K9: 01/14/07 J10: Monday K10: 01/15/07 J11: Tuesday K11: 01/16/07 and so on.... What formulas would I put in K9:K15 to do this? OR, is there a better way to get the same end result? TIA, Ray |
Calculate DATE from Weekday and Week #
See here for some ideas: http://www.ozgrid.com/Excel/day-of-week.htm
Dave -- Brevity is the soul of wit. "Ray" wrote: Hello - (XL2002 on XP) I'm building a template to report daily sales and am looking for a way to calculate the DATE for each day of the current week. Here's what I have so far: cell J3: =NOW() (formatted to MM/DD/YY) K3: week # (calc'd using a really long formula, TOO long to write here) J9-J15: the days of the week K9-K15: this is where I want the dates for the current week to go So, as a real example, right the values should be: J3: 01/16/07 K3: 0703 J9: Sunday K9: 01/14/07 J10: Monday K10: 01/15/07 J11: Tuesday K11: 01/16/07 and so on.... What formulas would I put in K9:K15 to do this? OR, is there a better way to get the same end result? TIA, Ray |
Calculate DATE from Weekday and Week #
Try this:
=J$3-WEEKDAY(J$3)+ROWS($1:1) Copy down as needed. Biff "Ray" wrote in message oups.com... Hello - (XL2002 on XP) I'm building a template to report daily sales and am looking for a way to calculate the DATE for each day of the current week. Here's what I have so far: cell J3: =NOW() (formatted to MM/DD/YY) K3: week # (calc'd using a really long formula, TOO long to write here) J9-J15: the days of the week K9-K15: this is where I want the dates for the current week to go So, as a real example, right the values should be: J3: 01/16/07 K3: 0703 J9: Sunday K9: 01/14/07 J10: Monday K10: 01/15/07 J11: Tuesday K11: 01/16/07 and so on.... What formulas would I put in K9:K15 to do this? OR, is there a better way to get the same end result? TIA, Ray |
Calculate DATE from Weekday and Week #
=TADAY()
That's literally how we pronounce it here in southwestern Pennsylvania! creek = crick you guys = youns or yunz bologna = jumbo Biff "Sandy Mann" wrote in message ... I would suggest that you use =TADAY() in J3 in place of NOW() which has the time included in it For K9 try: =J4-WEEKDAY(J4)+1 For K10 enter =K9+2 and copy down to K15 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Ray" wrote in message oups.com... Hello - (XL2002 on XP) I'm building a template to report daily sales and am looking for a way to calculate the DATE for each day of the current week. Here's what I have so far: cell J3: =NOW() (formatted to MM/DD/YY) K3: week # (calc'd using a really long formula, TOO long to write here) J9-J15: the days of the week K9-K15: this is where I want the dates for the current week to go So, as a real example, right the values should be: J3: 01/16/07 K3: 0703 J9: Sunday K9: 01/14/07 J10: Monday K10: 01/15/07 J11: Tuesday K11: 01/16/07 and so on.... What formulas would I put in K9:K15 to do this? OR, is there a better way to get the same end result? TIA, Ray |
Calculate DATE from Weekday and Week #
LOL.
One job I could never do is that of a proof reader! -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "T. Valko" wrote in message ... =TADAY() That's literally how we pronounce it here in southwestern Pennsylvania! creek = crick you guys = youns or yunz bologna = jumbo Biff "Sandy Mann" wrote in message ... I would suggest that you use =TADAY() in J3 in place of NOW() which has the time included in it For K9 try: =J4-WEEKDAY(J4)+1 For K10 enter =K9+2 and copy down to K15 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Ray" wrote in message oups.com... Hello - (XL2002 on XP) I'm building a template to report daily sales and am looking for a way to calculate the DATE for each day of the current week. Here's what I have so far: cell J3: =NOW() (formatted to MM/DD/YY) K3: week # (calc'd using a really long formula, TOO long to write here) J9-J15: the days of the week K9-K15: this is where I want the dates for the current week to go So, as a real example, right the values should be: J3: 01/16/07 K3: 0703 J9: Sunday K9: 01/14/07 J10: Monday K10: 01/15/07 J11: Tuesday K11: 01/16/07 and so on.... What formulas would I put in K9:K15 to do this? OR, is there a better way to get the same end result? TIA, Ray |
Calculate DATE from Weekday and Week #
Thanks very much Sandy ... works perfectly!
Can you explain what the formula in K9 is actually doing? I mean, I read the formula like this: take today's DATE subtract today's WEEKDAY plus one ..... or, in numbers, 1/17/07 - WEDNESDAY + 1 How does that formula equal Monday's date? In any event, it works -- and I thank you very much! br//ray Sandy Mann wrote: LOL. One job I could never do is that of a proof reader! -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "T. Valko" wrote in message ... =TADAY() That's literally how we pronounce it here in southwestern Pennsylvania! creek = crick you guys = youns or yunz bologna = jumbo Biff "Sandy Mann" wrote in message ... I would suggest that you use =TADAY() in J3 in place of NOW() which has the time included in it For K9 try: =J4-WEEKDAY(J4)+1 For K10 enter =K9+2 and copy down to K15 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Ray" wrote in message oups.com... Hello - (XL2002 on XP) I'm building a template to report daily sales and am looking for a way to calculate the DATE for each day of the current week. Here's what I have so far: cell J3: =NOW() (formatted to MM/DD/YY) K3: week # (calc'd using a really long formula, TOO long to write here) J9-J15: the days of the week K9-K15: this is where I want the dates for the current week to go So, as a real example, right the values should be: J3: 01/16/07 K3: 0703 J9: Sunday K9: 01/14/07 J10: Monday K10: 01/15/07 J11: Tuesday K11: 01/16/07 and so on.... What formulas would I put in K9:K15 to do this? OR, is there a better way to get the same end result? TIA, Ray |
Calculate DATE from Weekday and Week #
Hi Ray,
If you check in Help for WEEKDAY() you will see that it returns the day (number) of the week with Sunday = 1 and through to Saturday = 7. So if you subtract the WEEKDAY() from ANY date then you will get the date of the previous Saturday. You wanted the week to start on Sunday so we have to add 1 to that date to make it Sunday. Actually the WEEKDAY() function is more correctly written as =WEEKDAY(Date,1) because there are three options that can be used with an argument of 1, 2 or 3. WEEKDAY(Date,1) is the default and XL will assume the 1 if it is missed out. WEEKDAY() with a 2 argument returns Monday as 1 through to Sunday as 7 and with a 3 it returns Monday as zero and Sunday as 6 So from that it may look at first sight as if =J3-WEEKDAY(J3,2) would have been a better option to use because that, used with today's date, (or as Biff would say taday's date <g ), does return Last Sunday's date. However, when it gets to NEXT Sunday, =J3-WEEKDAY(J3,2) will still be returning the PREVIOUS Sunday not that day. That is why I chose to use the first option and add one day -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Ray" wrote in message ups.com... Thanks very much Sandy ... works perfectly! Can you explain what the formula in K9 is actually doing? I mean, I read the formula like this: take today's DATE subtract today's WEEKDAY plus one ..... or, in numbers, 1/17/07 - WEDNESDAY + 1 How does that formula equal Monday's date? In any event, it works -- and I thank you very much! br//ray Sandy Mann wrote: LOL. One job I could never do is that of a proof reader! -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "T. Valko" wrote in message ... =TADAY() That's literally how we pronounce it here in southwestern Pennsylvania! creek = crick you guys = youns or yunz bologna = jumbo Biff "Sandy Mann" wrote in message ... I would suggest that you use =TADAY() in J3 in place of NOW() which has the time included in it For K9 try: =J4-WEEKDAY(J4)+1 For K10 enter =K9+2 and copy down to K15 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Ray" wrote in message oups.com... Hello - (XL2002 on XP) I'm building a template to report daily sales and am looking for a way to calculate the DATE for each day of the current week. Here's what I have so far: cell J3: =NOW() (formatted to MM/DD/YY) K3: week # (calc'd using a really long formula, TOO long to write here) J9-J15: the days of the week K9-K15: this is where I want the dates for the current week to go So, as a real example, right the values should be: J3: 01/16/07 K3: 0703 J9: Sunday K9: 01/14/07 J10: Monday K10: 01/15/07 J11: Tuesday K11: 01/16/07 and so on.... What formulas would I put in K9:K15 to do this? OR, is there a better way to get the same end result? TIA, Ray |
Calculate DATE from Weekday and Week #
Have you looked at Help to see what numbers the WEEKDAY() function returns?
-- David Biddulph "Ray" wrote in message ups.com... Thanks very much Sandy ... works perfectly! Can you explain what the formula in K9 is actually doing? I mean, I read the formula like this: take today's DATE subtract today's WEEKDAY plus one ..... or, in numbers, 1/17/07 - WEDNESDAY + 1 How does that formula equal Monday's date? In any event, it works -- and I thank you very much! |
Calculate DATE from Weekday and Week #
Hello again -
Makes perfect sense ... thanks for the clear explanation! rgds, ray Sandy Mann wrote: Hi Ray, If you check in Help for WEEKDAY() you will see that it returns the day (number) of the week with Sunday = 1 and through to Saturday = 7. So if you subtract the WEEKDAY() from ANY date then you will get the date of the previous Saturday. You wanted the week to start on Sunday so we have to add 1 to that date to make it Sunday. Actually the WEEKDAY() function is more correctly written as =WEEKDAY(Date,1) because there are three options that can be used with an argument of 1, 2 or 3. WEEKDAY(Date,1) is the default and XL will assume the 1 if it is missed out. WEEKDAY() with a 2 argument returns Monday as 1 through to Sunday as 7 and with a 3 it returns Monday as zero and Sunday as 6 So from that it may look at first sight as if =J3-WEEKDAY(J3,2) would have been a better option to use because that, used with today's date, (or as Biff would say taday's date <g ), does return Last Sunday's date. However, when it gets to NEXT Sunday, =J3-WEEKDAY(J3,2) will still be returning the PREVIOUS Sunday not that day. That is why I chose to use the first option and add one day -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Ray" wrote in message ups.com... Thanks very much Sandy ... works perfectly! Can you explain what the formula in K9 is actually doing? I mean, I read the formula like this: take today's DATE subtract today's WEEKDAY plus one ..... or, in numbers, 1/17/07 - WEDNESDAY + 1 How does that formula equal Monday's date? In any event, it works -- and I thank you very much! br//ray Sandy Mann wrote: LOL. One job I could never do is that of a proof reader! -- Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "T. Valko" wrote in message ... =TADAY() That's literally how we pronounce it here in southwestern Pennsylvania! creek = crick you guys = youns or yunz bologna = jumbo Biff "Sandy Mann" wrote in message ... I would suggest that you use =TADAY() in J3 in place of NOW() which has the time included in it For K9 try: =J4-WEEKDAY(J4)+1 For K10 enter =K9+2 and copy down to K15 -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Ray" wrote in message oups.com... Hello - (XL2002 on XP) I'm building a template to report daily sales and am looking for a way to calculate the DATE for each day of the current week. Here's what I have so far: cell J3: =NOW() (formatted to MM/DD/YY) K3: week # (calc'd using a really long formula, TOO long to write here) J9-J15: the days of the week K9-K15: this is where I want the dates for the current week to go So, as a real example, right the values should be: J3: 01/16/07 K3: 0703 J9: Sunday K9: 01/14/07 J10: Monday K10: 01/15/07 J11: Tuesday K11: 01/16/07 and so on.... What formulas would I put in K9:K15 to do this? OR, is there a better way to get the same end result? TIA, Ray |
| All times are GMT +1. The time now is 09:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com