Date and time

[jono2494]

Hello, Hopefully soon can help me.

I am trying to work out how to create a formula to make a plan showing when a total process will be finished and also each stage start and finish times. The time for each stage is fixed, but the initial start time may vary and the process in total will span 3 days.

I am happy on how many minutes it takes from the start to finish, but am struggling to add this to a date.

For example

Stage 1 was started at 0700 on the 18th of Nov and it takes 60 mins so finished at 0800 on the 18th of November

Stage 2 can only start when 1 is finished so it starts at 0800 and takes 24 hours (1440 mins) so should finish on 19th of November at 0800.

Stage 3 takes 12 hours (720 mins) so will finish at 2000 hours on the 19th.

I want the once cell to have both the start cells and the finish cells to have the date and the time in it based on the duration of the stage before.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Stage[/TD]
[TD]Start[/TD]
[TD]Duration[/TD]
[TD]Finish[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0700 18-11-18[/TD]
[TD]60[/TD]
[TD]0800 18-11-18[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0800 18-11-18[/TD]
[TD]1440[/TD]
[TD]0800 19-11-18[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0800 19-11-18[/TD]
[TD]720[/TD]
[TD]2000 19-11-18[/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes sense

 

[SpillerBD]

As long as you date/time is stored as a serial number and not text, it is easy as long as you maintain the right unit conversions.

FinishDateTime = StartDateTime + (DurationInMinute/1440)

You could also record the duration in hh:mm and avoid the unit conversion.

 

[James006]

Hello,

Quite a tricky question ...

By the way, it does require that you explicit your Working Hours ...

 

[jono2494]

Thank you for the reply. I think i am happy with the formula, but was struggling to get one cell to recognise both a date and time in the same cell. I want the first cell to Start on eg, Sunday 18th of Nov at for example 7 am. I seem to be able to only get the DD/MM/YY or the HH/MM, Is there a way of getting the cell to recognise it as a date and time. How would I get the cell A1 to read 18 Nov 18 0700?

I would then work the formulas off of this to work out what time and date stage starts and finishes.

Hopefully this makes sense.

 

[SpillerBD]

IF your TEXT value is consistently formatted you can convert the text to an Date/Time serial value using

=DATEVALUE(LEFT(A1,10))+TIME(MID(A1,12,2),RIGHT(A1,2),)

 

[jono2494]

Thank you, this does work, but trying to make the sheet simple as several users use this. I was hoping to simply type in 08-11-18 0700 and click the number formatting and customise to include date and time. Seems this can not work. Thank you for your help as the date value will be useful in another sheet.
Best wishes