Enter remaining hours data in cell based on TODAY for future dates (week columns)

[Ronin7424]

**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

[#2]

@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.

[#3]

@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.

[#4]

Thanks, you're right I don't have that option. I'll look into uploading it to a sharing site. Appreciate your help!

[#5]

@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. 

[#6]

@Joe User @NowshadAhmed 

Here is a link to the actual spreadsheet. Thank you! 

Resource_Plan_Template 

Please let me know if there is an issue with the link. 

[#7 · Accepted solution]

@Ronin7424 

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))

[#8]

@Joe User

Thank you! That worked perfectly! Appreciate your time.