Jump to content


Photo

Dynamically expanding a range of Fridays based on Today's date

DATE TODAY SUMIFS WEEKDAY EOMONTH

  • Please log in to reply
8 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 07 July 2016 - 11:10 PM

Jonathan, I'm really trying to implement the various things you've taught me, but this one is just baffling me.  :(
 
Primary Goal
The tab Work Log is where a user will be entering the number of hours for a time period ending on Friday (all dates in row 4 are Fridays).
 
On the tab Time Reporting, I need to calculate just the number of hours entered on Work Log up to the following Friday. These hours are totaled in the column Hours (P) that correspond to the month that I'm in (July).
 
For example, using today's date, July 7, I want to have cell L6 on Time Reporting include the value in cell G5 on Work Log. Tomorrow, July 8, I then want L6 on Time Reporting to add the value in G5 (Work Log) to the value in cell H5 (Work Log), which is July 15. The process continues by adding each consecutive value up to the following Friday as long as today's date is greater than (not equal to) the previous Friday. Right now, the L6 has a fixed range, which was the way I received the file. But foreseeing the problem with expanding date ranges, I'm trying to keep this dynamic. 
 
Another way of putting it, the MAX date will be the Friday ahead of today's date but only if today's date is older than the previous Friday AND within the same month. This formula will be carried down to the rows beneath it for the corresponding roles identified in column B on Time Reporting.
 
Now, once July is completed, that's when cell R5 on Time Reporting will kick in, but it will only look at the values entered for the Fridays in July on Work Log. So fast-forward to July 29, the last Friday of the month. Cell R5 is for August hours. So it will not look at the values for the previous values in July. Instead, it will now look at only the values entered on Work Log for August and total them in the same way I've described above.
 
Secondary Goal
On Time Reporting, columns F through K are totaling the corresponding columns for the months to the right of the spreadsheet (columns L onward). Cell F6 is my attempt at starting a dynamic range using OFFSET, but I'm having a problem figuring out how to look just at the columns/cells that I want.
 
If you look at cell F7, you'll see the original formula of simply selecting each cell to add up to the total. That's too manual. I want it to automatically identify the cells that it should include for the total. Please note that the number of columns can shrink or expand depending on how many months the user wants to record. And just like described above, this formula will carry down for the other rows and for the relative roles defined in column B. But unlike above, I don't need to be concerned about the months here because this will have already been addressed in Primary Goal above.
 
So to clarify, Hours (P) in column F should calculate all of the columns labeled Hours (P) to the right, which are for each month; Hours (A) (cell H6) will do the same for all of the columns Hours (A) to the right. I do not need to do this for Hours (V) or any of the columns with Cost in them (columns G and I). Each month will always have the same set of columns for Planned hours, Planned cost, Actual hours, actual costs, etc.
 
I hope this makes sense, but if not, please let me know how I can clear things up. And thank you very much for your assistance.
 
Attached File  dynamic date ranges and calculations.xlsx   210.96KB   67 downloads
 

 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 08 July 2016 - 01:15 PM

Hi phoenixrizing,

 

Your primary goal sounds like something that could be achieved using the SUMIF function. If you wanted to sum all of the hours from your Work Log sheet that are on dates earlier than today's date, you could use this formula:

=SUMIF($G$4:$B$N4,"<="&TODAY(),G5:BN5)

 

You could combine this with your formula to get the date of the next Friday to get all hours less than or equal to next Friday.

 

You can find a more in-depth explanation of the SUMIF function in the video lesson: Expert Skills Lesson 3-6 Create an Excel SUMIF function and Excel COUNTIF function.

 

Your secondary goal should be achievable, but you might be able to do all of this much more easily by restructuring your data and using pivot tables to create the summaries you need.

 

First of all though, you should be able to solve the problem in your existing worksheet by once again using the SUMIF function like this: =SUMIF($L$5:$BM$5,"Hours (P)",L6:BM6)

 

This will total all of the Hours (P) columns, enabling you to get a total of your hours.

 

As I said, your data is currently being recorded in a format similar to a pivot table. In essence, it's already in a summarized format, which makes it difficult to create further summaries from it.

 

I've attached a simplified example workbook showing how you could 'unpivot' your data and then use a pivot table to summarize it:

 

Attached File  dynamic date ranges and calculations.xlsx   161.12KB   61 downloads

dynamic date ranges and calculations.png

 

You can see how to create a pivot table in the video lesson: Expert Skills Lesson 5-1 Create a pivot table.

 

One last thing that may be of interest is that Excel 2016's new Get & Transform feature offers the ability to unpivot data, making it possible to automatically create an 'unpivoted' version of your data that can be refreshed whenever the source data changes. I still wouldn't recommend using a 'pivoted' format for data entry, but it is a very useful tool when working with data that is in a pivoted format.

 

The new Get & Transform features are fully explained in our Excel 2016 course. We don't currently offer a video version of this course, but it can be purchased in printed book and e-book format from our books page.

 

I hope this helps you to achieve your goals, but please feel free to reply if I can offer any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 08 July 2016 - 10:19 PM

Jonathan, I agree with you about the similarity to pivot tables. While I haven't given up on the idea, I think it's going to take some time for my users to become more familiar with that concept. I definitely see the value in that path though.
 
Until then, I took your SUMIFS formula and tried to modify it to work on capturing the values on Work Log. It works just fine in the totals columns.
 
Conceptually, I know I need to have the range expand to include the values for the upcoming Friday whenever today's date is greater than the last Friday and less than the upcoming Friday.
 
Starting with L6 on Time Reporting, that will always look at the values in June on Work Log (or whatever the month is), so currently columns G through I.
 
The part I can't figure out is how to tell it to include the upcoming Friday when the criteria I described above has been met. I've tried using MAX, OFFSET, LARGE, SMALL, INDEX and MATCH, but I cannot get it solidified into a working formula.
 
Ideally, I'd like the start and stop of the date ranges on Work Log, G4 and onward to the right, to be identified within the formula. This way my users have less chance of messing things up on Time Reporting whenever they have to adjust for their project start date.
 
So I'm attaching a more fleshed-out version of my previous file to give a better sense of what I'm up against. Please let me know if you need additional clarification.
 
As always, thank you so much for your help.
 


#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 11 July 2016 - 11:53 AM

Hi phoenixrizing,

 

I think you may be going astray with the idea that you need to have the range expand or contract. The whole purpose of the SUMIF function is that you can use it on the entire range and use logical operators to control which cells are actually totaled.

 

In your Time Reporting workbook, it looks like you're just trying to get totals for each month. This means all you need is a SUMIFS function covering the entire range of hours in the Work Log workbook, along with criteria to only sum hours between the 1st of July and the 1st of August.

 

SUMIFS is just like SUMIF, but allows you to specify multiple criteria instead of just one.

 

Here's an example:

=SUMIFS('Work Log'!$G5:$BN5,'Work Log'!$G$4:$BN$4,">="&DATE(YEAR(L3),MONTH(L3),1),'Work Log'!$G$4:$BN$4,"<"&DATE(YEAR(L3),MONTH(L3)+1,1))

 

This models the following logic:

 

Sum the values in cells G5:BN5 (the column for the Project Manager) for the following criteria:

The dates in cells G4:BN4 are greater than or equal to the 1st day of the month specified in cell L3

The dates in cells G4:BN4 are less than or equal to the 1st day of the month after the one specified in cell L3

 

If you want to review the SUMIF function, you can find the relevant video lesson in: Expert Skills Lesson 3-6 Create an Excel SUMIF function and Excel COUNTIF function.

 

You'll also notice that I've used mixed cell references such as $G5:$BN5 in order to allow the formulas to be filled down and across without losing track of the cells they should affect. You can review how mixed cell references work in the video lesson: Essential Skills Lesson 3-13 Understand mixed cell references.

 

If you need these formulas to remain valid even if more data is added to the workbook, you might prefer to expand the scope of the references. You could have them cover the entire possible width of the workbook like this:

=SUMIFS('Work Log'!$G5:$XFD5,'Work Log'!$G$4:$XFD$4,">="&DATE(YEAR(L3),MONTH(L3),1),'Work Log'!$G$4:$XFD$4,"<"&DATE(YEAR(L3),MONTH(L3)+1,1))

 

Column XFD is the last possible column of the workbook, so this formula would continue to work even if you completely filled the workbook (although it would probably take a while to recalculate the totals).

 

I have attached a copy of your workbook with the new formulas applied:

 

Attached File  dynamic date ranges and calculations_new file.xlsx   240.71KB   54 downloads

dynamic date ranges.png

 

One final thing to note is that this is another task that would be simplified by restructuring the data to run down the workbook instead of across, as doing so would allow you to use structured table references in your formulas. You can review structured table references in the video lesson: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

 

In any case, I hope this helps you to achieve your goal, and feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 12 July 2016 - 09:58 PM

Jonathan, this is fantastic! 
 
After implementing your formula, I tweaked it just a little to make a correction: the L3 reference wasn't locked onto row 3, but once I did, the numbers fell into place.
 
However, I should clarify that the formula should only take into account the values that fall within a smaller range to begin with -- not the entire month at once. Eventually, yes, they will cover the entire month, but only a week at a time.
 
Using cell L6 on Time Reporting as a start, I need to add the values on July 8 to the values in the column for July 15. Conceptually, today is greater than last Friday, but less than next Friday.
 
Once today becomes equal with next Friday (July 15 in this case), it will then add the values for July 22 to the values it has already captured for July 8 through July 15. And the process repeats from there for each week.
 
That formula in L6 currently looks at L3 for its reference date, but what I think I need is to have it reference the cells on Work Log starting with G4, each of which has the Friday date. If there's some way to incrementally add 7 days to each of those dates all in L6 on Time Reporting, then that would continuously expand the end date.
 
Conceptually speaking then, it would be something like "Take the MIN (which will always be July 8 in this case) and then look at ahead to the next Friday to be the MAX. When next Friday rolls around, start adding from the same MIN again, but increase the MAX by 7 to get the next Friday."
 


#6 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 13 July 2016 - 11:08 AM

Hi phoenixrizing,

 

It sounds like this shouldn't be too difficult using the formula to find the date of the next Friday that I mentioned in your previous thread.

 

I'm not sure if I quite understand your goal, though. I can tell that you're trying to use a date range, but I'm not quite sure if it's:

 

>= Today <= Next Friday

 

...or:

 

>= Today <= The last Friday in the month

 

The first option is simply:

=SUMIFS('Work Log'!$G5:$BN5,'Work Log'!$G$4:$BN$4,">="&TODAY()),'Work Log'!$G$4:$BN$4,"<="&TODAY()+(7-WEEKDAY(TODAY()+1)),1))

 

Finding the last Friday in the month is doable by using the WEEKDAY function again, just in a slightly different way.

 

The first thing you'll need to do is to find the last day of the current month. You could do this in two ways: either with the DATE function or the EOMONTH function.

 

The following formulas will both return the last day of the current month:

=EOMONTH(TODAY())

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1

 

You can see a complete explanation of the DATE function in the video lesson: Expert Skills Lesson 3-11 Use the Excel DATE function to offset days, months and years.

 

Now that you have the date of the last day of the month, you just need to find the Friday previous to that date. You can do this using the following formula:

=EOMONTH(TODAY())-WEEKDAY(EOMONTH(TODAY()))-1

 

Now all that's left is to incorporate this into your main formula:

=SUMIFS('Work Log'!$G5:$BN5,'Work Log'!$G$4:$BN$4,">="&TODAY()),'Work Log'!$G$4:$BN$4,"<="&EOMONTH(TODAY())-WEEKDAY(EOMONTH(TODAY()))-1,1))

 

I hope one of these two formulas is the one you were looking for, but feel free to reply if this isn't quite what you needed.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#7 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 13 July 2016 - 06:44 PM

Jonathan, we are so close and I truly appreciate your patience as we narrow this thing down.
 
It seemed that the formula you provided had an error so I removed an extra ) in the middle and removed the ,1 at the end. (it was the only way I could get it to work.
 
Your's
=SUMIFS('Work Log'!$G5:$BN5,'Work Log'!$G$4:$BN$4,">="&TODAY()),'Work Log'!$G$4:$BN$4,"<="&TODAY()+(7-WEEKDAY(TODAY()+1)),1))
 
Mine
=SUMIFS('Work Log'!$G5:$BN5,'Work Log'!$G$4:$BN$4,"<"&TODAY(),'Work Log'!$G$4:$BN$4,"<"&TODAY()+(7-WEEKDAY(TODAY()+1)))
 
 
The attached file demonstrates better what I'm trying to achieve. In short, yes, the last Friday of the month is important, but I need to incrementally lead up to that day, week by week.
 
Using the modified formula, I can get only the first Friday's worth of values, but I need it to include up to this upcoming Friday, July 15. Perhaps my modification messed that up?
 


#8 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 14 July 2016 - 09:54 PM

Hi phoenixrizing,

 

I'm still having trouble understanding the exact date range you're trying to achieve, but it seems like it boils down to this:

 

Start Date: The first Friday of the month.

Finish Date: The Friday prior to or on today's date.

 

Getting the first Friday of the month is simply a matter of applying the formula covered in your previous thread to the first day of the current month:

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)+(7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1))

 

This is exactly the same formula covered in your earlier thread, but using the DATE function to extract the 1st day of the current month, as covered in: Expert Skills Lesson 3-11 Use the Excel DATE function to offset days, months and years.

 

Getting the Friday on or prior to today's date is the same formula as shown earlier in this thread:

=TODAY()-WEEKDAY(TODAY())-1

 

Once again incorporating these new formulas into your original formula:

=SUMIFS('Work Log'!$G5:$BN5,'Work Log'!$G$4:$BN$4,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1)+(7-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1)+1)),'Work Log'!$G$4:$BN$4,"<="&TODAY()-WEEKDAY(TODAY())-1)

 

I have tested this formula in your workbook and it seems to be working correctly.

 

From your description it sounds like there might be a few more complications with the finish date, but this might be a good foundation to build on if it's closer to what you need.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#9 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 21 July 2016 - 12:24 AM

Thanks for your help, Jonathan. Much appreciated. :) 







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users