Hi all - I get lost with trying to use formulas and time. at work we have a production schedule and other things interrogate the time columns. I'll try and explain it: we always enter the production line start time in cell J6 and then cell I7 has how long that step takes and then J7 adds the time from I7 to J6 which then the "end time" for that step so on and so on. the formula works really well BUT.... it is assuming that the date is 0/01/1900
how can I set this up so that the date in J7 is current... in this example J7 should be 17/08/2020 5:00
I know it would work if we manually type into J6 14/08/2020 3:30.... but is there a way to get the right date by formula?
if it helps, the start of week date is available in cell E1
hope that made some sense.... thanks in advance.
how can I set this up so that the date in J7 is current... in this example J7 should be 17/08/2020 5:00
I know it would work if we manually type into J6 14/08/2020 3:30.... but is there a way to get the right date by formula?
if it helps, the start of week date is available in cell E1
hope that made some sense.... thanks in advance.
| Copy of 200831.xlsm | ||||
|---|---|---|---|---|
| I | J | |||
| 5 | Hrs | ST | ||
| 6 | 3:30 | |||
| 7 | 1:30 | 5:00 | ||
| 8 | 2:00 | 7:00 | ||
| 9 | 0:15 | 7:15 | ||
| 10 | 16:40 | 23:55 | ||
| 11 | 23:55 | |||
| 12 | 23:55 | |||
Week 2 Schedule | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I6:I12 | I6 | =IF(D6="","",F6/VLOOKUP(D6,'Product Data'!$A$4:$N$156,14,FALSE)/24) |
| J7:J12 | J7 | =IF(D7="",(J6),(J6+I7)) |
