How to auto-populate a number entered in one cell to another depending on date range

A

andreamoulton824

New Member
Joined
Apr 1, 2024
Messages
5
Platform
  1. Windows
Is it possible to auto-populate a number entered in one cell to another depending on a date range?
I am trying to make a Labor Tracker spreadsheet. We need to track our crew sizes as we have different jobs going on at the same time. We need to make sure our manpower is in control. Is this possible?
 

Attachments

  • Labor Tracker.png
    Labor Tracker.png
    112.7 KB · Views: 6

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
andreamoulton824 said:
Is it possible to auto-populate a number entered in one cell to another depending on a date range?
I am trying to make a Labor Tracker spreadsheet. We need to track our crew sizes as we have different jobs going on at the same time. We need to make sure our manpower is in control. Is this possible?
Click to expand...
welcome to the Mr. Excel forum. Can you give some better detail on the specifics? What date range? How is to be considered? And if you find the desired dates, what do you do in that calculation?

Also, if you can provide an xl2bb mini worksheet (see link below) it would be much more helpful than posting an image. If you can't post a mini worksheet, then post your data as a table that can be copied (it won't give us the formulas in your worksheeet like the xl2bb, but it is better than making the forum completely recreate your scenario).
 
Upvote 0
@awoohaw I am unable to download the XL2BB as I am on a work computer and need admin rights to do so. To explain further, for instance, my company is awarded a job. The job is going to last 10 days, Monday through Friday April 15 - April 26. We will need 3 crew members on this job for the 10 days. I have completed the information on the left hand side of my spreadsheet with this information. On the right hand side of my spreadsheet are the days of the week with totals running at the bottom. If jobs overlap, I want to be sure we have enough guys scheduled for those days where they overlap. Instead of having to input the crew size for each day, is there a way to link my cell on the left with the start and end dates with the days of the week on the right? Not even sure if that makes sense. You are asking me to post my data as a table that can be copied. I am not sure if I know how to do that properly.
 
Upvote 0
This may work:
edited for weekend:
Book2
ADEFGLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
2ProjectCrew SizeEst StartEst StopEst Wrk Days2024-04-012024-04-022024-04-032024-04-042024-04-052024-04-062024-04-072024-04-082024-04-092024-04-102024-04-112024-04-122024-04-132024-04-142024-04-152024-04-162024-04-172024-04-182024-04-192024-04-202024-04-212024-04-222024-04-232024-04-242024-04-252024-04-262024-04-272024-04-282024-04-292024-04-302024-05-012024-05-022024-05-032024-05-04
3A32024-04-152024-04-2610              33333  33333        
4B22024-04-202024-04-307                     22222  22    
Sheet4
Cell Formulas
RangeFormula
L3:AS4L3=IF(WEEKDAY(L$2,2)>5,"",IF(AND(L$2>=$E3,L$2<=$F3),$D3,""))
G3:G4G3=NETWORKDAYS.INTL(E3,F3)
 
Last edited:
Upvote 0
awoohaw said:
This may work:
edited for weekend:
Book2
ADEFGLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
2ProjectCrew SizeEst StartEst StopEst Wrk Days2024-04-012024-04-022024-04-032024-04-042024-04-052024-04-062024-04-072024-04-082024-04-092024-04-102024-04-112024-04-122024-04-132024-04-142024-04-152024-04-162024-04-172024-04-182024-04-192024-04-202024-04-212024-04-222024-04-232024-04-242024-04-252024-04-262024-04-272024-04-282024-04-292024-04-302024-05-012024-05-022024-05-032024-05-04
3A32024-04-152024-04-2610              33333  33333        
4B22024-04-202024-04-307                     22222  22    
Sheet4
Cell Formulas
RangeFormula
L3:AS4L3=IF(WEEKDAY(L$2,2)>5,"",IF(AND(L$2>=$E3,L$2<=$F3),$D3,""))
G3:G4G3=NETWORKDAYS.INTL(E3,F3)
Click to expand...
Thank you so much!
I am unable to get the L3 formula to work. I was able to input the formula in G3 and I like that auto function (thank you) but when I put in the L3 formula in cell L3 and drag to copy it through AM4, nothing populates.
 
Upvote 0
what version of excel do you have? You may need to commit the formula with the CNTL-SHFT-ENTR keystroke. (It will put curly braces around the formulas).


Book2
ADEFGLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
1
2ProjectCrew SizeEst StartEst StopEst Wrk Days2024-04-012024-04-022024-04-032024-04-042024-04-052024-04-062024-04-072024-04-082024-04-092024-04-102024-04-112024-04-122024-04-132024-04-142024-04-152024-04-162024-04-172024-04-182024-04-192024-04-202024-04-212024-04-222024-04-232024-04-242024-04-252024-04-262024-04-272024-04-282024-04-292024-04-302024-05-012024-05-022024-05-032024-05-04
3A32024-04-152024-04-2610              33333  33333        
4B22024-04-202024-04-307                     22222  22    
Sheet4
Cell Formulas
RangeFormula
L3:AS4L3=IF(WEEKDAY(L$2,2)>5,"",IF(AND(L$2>=$E3,L$2<=$F3),$D3,""))
G3:G4G3=NETWORKDAYS.INTL(E3,F3)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
I think I am running on Microsoft® Excel® for Microsoft 365 MSO (Version 2403 Build 16.0.17425.20058) 64-bit
 
Upvote 0
if you're on 365, the formula as I had in the original post should work. How did you put it into the cell? What you can do to make sure the formulas are working is just paste (click the copy icon in the top left corner of the "xl2bb" mini sheet(s) i posted into a brand new worksheet. If the formulas work well there, then you just need to manipulate it to work with your worksheet structure.

If you still have errors, then you really need to describe what the formula is and the error message. Ideally, the data in a table form (instead of image) to go along with it would help the forum debug for you.
 
Upvote 0
Thank you so much! I will try this this evening. I will reply in the morning/tomorrow if you are available. I really do appreciate your help.
 
Upvote 0
well, if i'm not here, another forum member may see and reply.
I'm on East Coast USA, so Eastern Daylight Time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Number Sequence based on date in a different cell
Replies
4
Views
72
Joe4
Joe4
A
  • Question
Find value from one workbook in another
Replies
4
Views
254
AndyEd
A
Q
  • Question
Creating a log that populates a name in a cell if a specific number is in another cell
Replies
3
Views
260
skillilea
S
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
197
bh24524
B
C
  • Question
Excel Linked Cells help
Replies
0
Views
236
CMcCormack1
C

Forum statistics

Threads
1,215,196
Messages
6,123,575
Members
449,108
Latest member
rache47

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top