Remaining time

[Martinpetersson]

I’m creating a tact time board and I’m trying to get an automatic updated cell with remaining work hours left on the work week.

For an example.

We work straight from Sunday 10 pm to Friday 2.30 pm
Our goal is to produce 12000 pieces in that time frame. (Approximately 113 hours)

I already have a live updated cell that shows how many pieces we have produced so far this week and also one cell that shows how many pieces there are left to produce.

I also have one cell that shows the tact we have produced in during the actual day. (Ex. 96 pieces per hour during this day)

I now want excel to calculate how many pieces we need to produce/hour during the hours that are left of this week.

If I only could get a cell that automatically shows spent time since Sunday 10 pm and remaining time to Friday 2.30 pm then it will work.

Please help.
Martin

 

[6StringJazzer]

It would be nice if you gave us actual Excel cell references so we could give you an actual Excel formula.

I have made assumptions as shown below. The bottom formula gives the pieces/hour required to make the goal.

$scratch.xlsm
	A	B
1	Pieces Produced This Week	6,000
2	Weekly Goal	12,000
3	Week Start	Sunday 4/24/2022 10:00 PM
4	Week End	Friday 4/29/2022 2:30 PM
5
6	Time left this week (hours)	46.8
7	Remaining to make goal:	6,000
8	Pieces per hour required to make goal:	128.2
Rate

Cell Formulas
Range	Formula
B3	B3	=TODAY()-WEEKDAY(TODAY(),1)+1+TIMEVALUE("10:00 PM")
B4	B4	=TODAY()-WEEKDAY(TODAY(),1)+1+5+TIMEVALUE("2:30 PM")
B6	B6	=ROUND(24*(B4-NOW()),1)
B7	B7	=B2-B1
B8	B8	=B7/B6

 

[Martinpetersson]

Thank you so much! I really appreciate it! Will look into it as soon as I come to work tomorrow.
I guess I just have to figurate out how the start and end date will automatically change every week so I don’t have to do it manually for each working week.

 

[6StringJazzer]

I already did that. Please see the formulas I provided.

 

[Martinpetersson]

Ok, thank you again for your service.
I think something is wrong because of my region.
I need to use isoweek and isoyear when I do things because of i live in Sweden, Europe.
i also had to change the "," to ";" in B6

I have tried to change to iso but it didn't work out for me. Would be very greatful if you could take a quick look again.
Please see attached mini sheet.
/Martin

tact_time.xlsx
	A	B	C
1	Pieces Produced This Week	9 500
2	Weekly Goal	12 200
3	Week Start	2022-04-28	Suppose to be Sunday.
4	Week End	2022-05-03	Suppose to be friday
5
6	Time left this week (hours)	126
7	Remaining pieces to make goal:	2700
8	Pieces per hour required to make goal:	21,4
Sheet1

Cell Formulas
Range	Formula
B3	B3	=TODAY()-WEEKDAY(TODAY()*0.1)+1+TIMEVALUE("10:00 PM")
B4	B4	=TODAY()-WEEKDAY(TODAY()*0.1)+1+5+TIMEVALUE("2:30 PM")
B6	B6	=ROUND(24*(B4-NOW()),1)
B7	B7	=B2-B1
B8	B8	=B7/B6

 

[bebo021999]

Is it correct? TODAY()*0.1

 

[Martinpetersson]

I just changed it to today()*0,3 now it works! Thank you so much.
couldn't have done it without you two.

 

[6StringJazzer]

You should not be multiplying TODAY by anything. Your formula should look like:

=TODAY()-WEEKDAY(TODAY();1)+1+TIMEVALUE("10:00 PM")

No *

I always show the U.S. format unless the question indicates a different localization.

 

[Martinpetersson]

OK, I see. Now it works good! thank you so much again!