Jan 22 2022 07:26 PM
**Can someone please tell me how to attach a file to the discussion thread?*** This is my first post and I don't see a link to attach a spreadsheet. Thank you.
Hello, first time using this site. Appreciate any help. In the attached spreadsheet, I am trying to add a formula or logic in column J which will calculate the remaining forecasted hours for the weeks in columns L-BL. In the first example, row 15, based on today's date of 1/22/2022, Polly Project Manager has 402.50 remaining hours in her allocation. I would like the sheet to automatically calculate and enter the result in Column J. Would need to apply the logic to all of Column J as information is entered.
I can't see a way to upload the spreadsheet so I have taken a screenshot of the worksheet. The highlighted cell is where I need the calculation.
System Information:
Microsoft Excel 365
PC on Windows 10
Jan 22 2022 07:58 PM
@Ronin7424 wrote: ``Can someone please tell me how to attach a file to the discussion thread?``
Look for the "browse" link at the bottom of the reply pane.
But you might not see that; or it might not be functional.
I've been told that not all users have that ability (sigh). It might depend on your new-participant status (double sigh).
If that is the case, upload your Excel file to a file-sharing website, and post the public download URL in response here. I like box.net/files; others like dropbox.com.
Jan 22 2022 08:03 PM
@Ronin7424 The information is not enough but let's ask a few questions:
What formula are you using to calculate the Remaining Hours? Or how are you calculating this?
How is the data represented in Columns L-BL?
Why is the 402.5 hours being represented in decimals rather than hours and minutes? Are all data in this format?
Note: on replying, you can see an option/link below saying 'Open full text editor'. And then follow what @Joe User instructed.
Jan 23 2022 07:59 AM
Jan 23 2022 08:03 AM
@NowshadAhmed Thanks for your help. This is my first discussion thread so apologies for lack of context.
I do not currently have a formula for remaining hours. Users are entering it manually. That is what I'm looking to solve for. It's simply the sum of the hours listed in columns L-BL but only for weeks after today's date. I would like for the sheet to automatically calculate the remaining hours.
The 402.5 is the total remaining hours for the year. Each resource works 37.5 hours per week (full-time). Yes, all the data is in this format.
Thanks again for the help.
Jan 23 2022 08:09 AM
Here is a link to the actual spreadsheet. Thank you!
Please let me know if there is an issue with the link.
Jan 23 2022 09:13 AM - edited Jan 23 2022 09:45 AM
Solution
Ostensibly, enter the following formula into J15 and copy down the column (see ERRATA below):
=G15 - SUMIF($L$14:$BL$14, "<" & TODAY(), L15:BL15)
Improvement:
=IF(N(G15)=0, "", G15 - SUMIF($L$14:$BL$14, "<" & TODAY(), L15:BL15))
-----
ERRATA.... It is better to enter the formula =TODAY() into one cell (K9), then reference that cell. For example:
=IF(N(G15)=0, "", G15 - SUMIF($L$14:$BL$14, "<" & $K$9, L15:BL15))
Jan 23 2022 12:40 PM
Jan 23 2022 09:13 AM - edited Jan 23 2022 09:45 AM
Solution
Ostensibly, enter the following formula into J15 and copy down the column (see ERRATA below):
=G15 - SUMIF($L$14:$BL$14, "<" & TODAY(), L15:BL15)
Improvement:
=IF(N(G15)=0, "", G15 - SUMIF($L$14:$BL$14, "<" & TODAY(), L15:BL15))
-----
ERRATA.... It is better to enter the formula =TODAY() into one cell (K9), then reference that cell. For example:
=IF(N(G15)=0, "", G15 - SUMIF($L$14:$BL$14, "<" & $K$9, L15:BL15))