Date Changes After Midnight

[bern75]

We have certain processes at work that have several steps and also span several days. We often take too much time for one step which in turn eats into the time that we have for other steps. I am developing a spread sheet that allows us to ensure that we only spend a certain time on each step. I start by entering the time that we are beginning the process and how much time we have for the whole process. The spreadsheet then allocates how much time we will have for each step and the time it ends. The problem I have is that it only shows the time and sometimes these steps can go over several days so I would like to have the date represented also. This is where I run into a problem.

I currently have the time in one column and the date in another. I have been able to combine them into one cell but have backed away from that because I am having trouble adding a date/time cell.

I tried this one formula below but it doesn’t work because after it changes the date once when it passes midnight it just keeps adding a new day even when it is not needed =IF(B2+C2>=1,A2+1,A2) (A2 = Start Date, B2 = Start Time, Shift).

I have been stuck for this for a long time and any help would be greatly appreciated, thanks!!!!

P.S. I also have another sheet in the same workbook that I use for certain formulas so if I need to do something out of the ordinary, I have room.

Q: How do I have the date accurately change after the time rolls into another new day.

Starting Date:

[TABLE="align: left"]
<tbody>[TR]
[TD="colspan: 2"]Beginning Time
[/TD]
[TD="colspan: 3"]Beginning Date
[/TD]
[TD="colspan: 2"]Time for Whole Process
[/TD]
[/TR]
[TR]
[TD]Hour
[/TD]
[TD]Minute
[/TD]
[TD]Day
[/TD]
[TD]Month
[/TD]
[TD]Year
[/TD]
[TD]Hour
[/TD]
[TD]Minute
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]24
[/TD]
[TD]14
[/TD]
[TD]11
[/TD]
[TD]2018
[/TD]
[TD]48
[/TD]
[TD]27
[/TD]
[/TR]
</tbody>[/TABLE]





Displayed Data:

[TABLE="width: 623"]
<tbody>[TR]
[TD]Steps
[/TD]
[TD]Percentage Allocated
for Each Step
[/TD]
[TD]Time Allocated
for Each Step
[/TD]
[TD]Time Step Will
Be Completed
[/TD]
[TD]Date Step Will
Be Completed
[/TD]
[/TR]
[TR]
[TD]Step 1:
[/TD]
[TD]5%
[/TD]
[TD]1:11
[/TD]
[TD]15:36
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Step 2:
[/TD]
[TD]20%
[/TD]
[TD]4:47
[/TD]
[TD]20:24
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Step 3:
[/TD]
[TD]10%
[/TD]
[TD]2:23
[/TD]
[TD]22:48
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Step 4:
[/TD]
[TD]30%
[/TD]
[TD]7:11
[/TD]
[TD]6:00
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Step 5:
[/TD]
[TD]5%
[/TD]
[TD]1:11
[/TD]
[TD]7:12
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Step 6:
[/TD]
[TD]15%
[/TD]
[TD]3:35
[/TD]
[TD]10:48
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Step 7:
[/TD]
[TD]15%
[/TD]
[TD]3:35
[/TD]
[TD]14:24
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

The date will really help, why not just add the date & time within the formula?

Gaz

 

[bern75]

I originally didn't about the date, just the time. Once I finished that is when I noticed that I should add the date. When you said "add the date & time within the formula" what do you mean by that? I tried to do a date/time combine into one cell using the =TEXT(A2,"m/dd/yy ")&TEXT(B2,"hh:mm:ss"). I haven't been able to add using this process and I'm not sure if it is because the date and time was cmbined as a text. Any help you could give is appreicated, thanks.