Round dates to next Saturday

[bam]

Hello, I have a record of defects entered for the last year. All defect
entrys have a date and timestamp.

I'd like to report on defects entered per week (the period of Sunday thorugh
Saturday). Are there any formulas I can use to convert (or copy) the entry
date and time to a week ending date?

Example, defects entered on Jan 1, 2, 3,4,5, 6, and 7 are converted to date
entered as 1/7/06
Defects entered on Jan 8, 9, 19,11, 12, 13, and 14 are converted to date
entered as 1/14/06
Etc.

Thanks in advance

Bart

 

[Don Guillett]

Yes, but why? It may be better to leave them alone to do what you want.

 

[bam]

I want to look at trends by week over time.

I create pivot tables with the data -- but I don't know how to display
trends over time -- I get a long list of defects (i.e. entered 1/2/2006
12:43 pm, 1/2/2006 12:55pm, etc) as opposed to (44 entered week of 1/7, 12
entered week of 1/14).

Ideas?

 

[Dave Peterson]

Maybe...

=IF(A1="","",A1+6-MOD(A1-1,7))

Hello, I have a record of defects entered for the last year. All defect
entrys have a date and timestamp.

I'd like to report on defects entered per week (the period of Sunday thorugh
Saturday). Are there any formulas I can use to convert (or copy) the entry
date and time to a week ending date?

Example, defects entered on Jan 1, 2, 3,4,5, 6, and 7 are converted to date
entered as 1/7/06
Defects entered on Jan 8, 9, 19,11, 12, 13, and 14 are converted to date
entered as 1/14/06
Etc.

Thanks in advance

Bart

 

[Pete_UK]

An alternative:

=INT(A1)+7-WEEKDAY(A1)

assuming your reported date is in A1.

Hope this helps.

Pete

 

[bam]

Wow, works great.
Thanks, very much!

Bart

 

[bam]

Yep, it works, thanks very much,

Bart

Maybe...

=IF(A1="","",A1+6-MOD(A1-1,7))

 

[Pete_UK]

You're welcome, Bart, thanks for feeding back.

Pete

 

[Dana DeLouis]

trends over time -- I get a long list of defects (i.e. entered 1/2/2006

12:43 pm, 1/2/2006 12:55pm, etc) as opposed to (44 entered week of 1/7, 12
entered week of 1/14).

Hi. You were so close. Pivot tables are a great way to do this.
In your Pivot table, drag your Date Column to the Row field.
This sounds like you already did that. Now, right click somewhere in that
long list of dates in the pivot table, and select "Group and Show Detail" ,
then "Group"
Select "Days", and make the increment "7" for weeks.
Make the start day the first previous Sunday from your earliest date.
This should group the dates just like you wanted. :>)

 

[bam]

I get a "cannot group that selection message'

Hi. You were so close. Pivot tables are a great way to do this.
In your Pivot table, drag your Date Column to the Row field.
This sounds like you already did that. Now, right click somewhere in that
long list of dates in the pivot table, and select "Group and Show Detail"
, then "Group"
Select "Days", and make the increment "7" for weeks.
Make the start day the first previous Sunday from your earliest date.
This should group the dates just like you wanted. :>)

 

[Dave Peterson]

Check for text and/or blank cells in that field. They all have to be dates.