Subtracting Time Malfunction

[Manny Singh]

Hi All,

I am trying to subtract time as per attached sheet, but it's not giving correct result.

Please tell me what I am doing wrong?

Regards,
Manny

 

[GraH - Guido]

Why did you use MOD? It seems to work without it.

 

[NARAYANK991]

Hi ,

The problem is that some start times have date + time values ; the same is the case for the end times.

Either all start times and all end times should have dates along with times , or all of them should have only time values.

Narayan

 

[NARAYANK991]

Hi ,

Two of the start times are :

06-01-1900 06:00:00

while the end times are :

04-01-1900 16:00:00

The end time cannot be less than the start time.

Narayan

 

[GraH - Guido]

Saw that too, but correcting only that does not seem to add up working time per week.
I Also the formatted total time like "[h]:mm:ss." Where the result is then 41:00:00 for JOB No. 3900.

 

[p45cal]

Does
=SUMPRODUCT(MOD(E8:E14,1)-MOD(D8:D14,1))
do it for you?

 

[Manny Singh]

Saw that too, but correcting only that does not seem to add up working time per week.
I Also the formatted total time like "[h]:mm:ss." Where the result is then 41:00:00 for JOB No. 3900.

Hi Grah,

I have tried your suggestion and still not getting correct result, could you please work your magic and send me the sheet?

 

[NARAYANK991]

Hi ,

You have not changed the formula , and you have not changed the cell format.

Using MOD will never give the correct result , since the purpose of using MOD 1 is to return only the decimal value ; thus , if a value is 2.725 , taking the MOD 1 of this will return 0.725 , which is not what you want.

Implement both of these , and you will get the correct result.

Narayan

 

[Manny Singh]

Hi ,

You have not changed the formula , and you have not changed the cell format.

Using MOD will never give the correct result , since the purpose of using MOD 1 is to return only the decimal value ; thus , if a value is 2.725 , taking the MOD 1 of this will return 0.725 , which is not what you want.

Implement both of these , and you will get the correct result.

Narayan

Thanks Narayan, I hadn't formatted totals row thus the mistake and thanks for educating on MOD, much appreciated.