help needed to calculate dates and time together

[VISHAL120]

I am actually having some problem to figure out the formula of how calculate specific date and time together. As everytime i am making the calculation is changing the dates but not the time.

Here is an example for better understanding.

in col A i will place my start date and time suppose its 29/08/20 9:20 AM

In col B the time to complete a task is 2 hours only and in column C its 1 day to complete so when calculation is done it shall display as follows:

IN Col B it shall show 29/08/20 11:20 AM [ as it add the 2 hours for the task to complete and in col C it shall display as 30/08/20 11:20 AM thus adding the 1 day and keeping the same time as from col B.

i have been trying this since long and everytime its adding only on the date but not changing the time.

I will be very grateful if you can guide me on this formula please.

Thank you in advance.:banghead:

[mse330]

Hi Vishal,

Based on your above example, how are you adding the 2 hours to the value of column A ? Try below
cell A1 : 29-08-2020 9:20:00 AM
cell B1 : =A1+(1/24*2)
cell C1 : =B1+1

[VISHAL120]

Hi Mse330,

thank you it seems working i will try it on the file and confirm. thank you again .

[Paul_Hossler]

Possibly a little more straight forward ....


cell B1 : =A1+TIMEVALUE("00:02:00")

[SamT]

MSE330's method would be hard to use if you need to add/subtract any value other than simple hours.

Paul's method is straight forward to add/subtract any time value from 00:00:01 to 23:59:59

[VISHAL120]

Thanks a lot for all the help.

i am just trying it.

as I mentioned in Col C we have to also calculate it with day that 1 day instead of hours. As right now when am checking it it doing with hours only with Paul method

[VISHAL120]

thank you both works well. solved

[VISHAL120]

Hello,

I have tried the formula on the file but I am having some problems like when the date is a holiday its still taking on the calculation and also the number of hours worked is 10 hrs suppose that we finish at 17:00 but when the calculation is doing its going beyond this time.

I have tried using the formula WORKDAY.INTL($K15+(1/24*LEADTIME!$B$4),11,1,HOLIDAY_RANGE) but it not giving the time now its as follow Wed/09/09 T :00:00

Am attaching a sample of the file for better understanding.

Row 10 to 13 I have place the formula as suggested by Mse330 as the formula propose by Paul_Hossler is good also but when I want to take the time hours from the leadtime sheet its make error.
ON column Q row 11 we see it shows Tue/25/08 T :21:00 in fact it shall have been Tue/26/08 T :11:00 as Tue/25/08 T :16:00 + 5 hrs from cell P11 will make Tue/25/08 T :21:00 BUT since we worked from 7:00 to 17:00 therefore it shall show Tue/26/08 T :11:00. This am unable to put a condition to make the calculation as such.

And the data from row 15 to 18 I have try to place the formula workday, workday.intl to remove the dates which are suppose to be holidays but this time its not showing the time.

Many thanks in advance if you can guide on this. As now am thinking to do the calculation of the dates and time separately if not possible to do both together.:banghead: