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: 14

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
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

M
  • Question Question
Populate a table of items based on 1 cell selection
Replies
1
Views
51
Edgar_
Edgar_
N
  • Question Question
Auto Training Tracker w/MS Forms
Replies
6
Views
387
Snakehips
Snakehips
J
  • Question Question
Filtering Results in List Box
Replies
2
Views
140
JohnS_323
J
M
  • Solved
Colour Changing Cells depending on number of clicks
Replies
8
Views
506
Peter_SSs
Peter_SSs
N
  • Solved
Number Sequence based on date in a different cell
Replies
4
Views
258
Joe4
Joe4

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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