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

Occasional Contributor

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

7 Replies

@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; others like

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

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

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


@Joe User @NowshadAhmed 

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



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

best response confirmed by Ronin7424 (Occasional Contributor)



Ostensibly, enter the following formula into J15 and copy down the column (see ERRATA below):


=G15 - SUMIF($L$14:$BL$14, "<" & TODAY(), L15:BL15)




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

@Joe User


Thank you! That worked perfectly! Appreciate your time.