Date and time

jono2494

New Member
Joined
Oct 1, 2013
Messages
7
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.

StageStartDurationFinish
10700 18-11-18600800 18-11-18
20800 18-11-1814400800 19-11-18
30800 19-11-187202000 19-11-18

<tbody>
</tbody>

Hope this makes sense
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

Quite a tricky question ...

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

jono2494

New Member
Joined
Oct 1, 2013
Messages
7
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

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
IF your TEXT value is consistently formatted you can convert the text to an Date/Time serial value using
Code:
=DATEVALUE(LEFT(A1,10))+TIME(MID(A1,12,2),RIGHT(A1,2),)
 

jono2494

New Member
Joined
Oct 1, 2013
Messages
7
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,827
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top