Formula: Time difference and sum of times [SOLVED]

[inddon]

Hello There,

I have 3 times: Time 1 (from yesterday), Time 2 & Time 3 (from today.)

I am adding times 2 & 3, which goes okay from the below solved post:
http://forum.chandoo.org/threads/formula-problem-in-totalling-times-solved.20296/

The problem lies in getting:

1. Time difference between today and yesterdays time (i.e. Time 1 & Time 3)
2. Sum of the total of time (Total Time 2 & 3 and Total Time 1 & 3)

I have attached the sample workbook for your reference.

Appreciate your advise and help.

Thanks & regards
Don

 

[NARAYANK991]

Hi Don ,

I am confused as to what exactly you wish to do.

1. You have 3 time values in cells B6 , D6 and E6 ; are these times as in 10:00 AM or 3:00 PM or are they hours as in 7:30 signifying 7 hours and 30 minutes ?

2. The value in G6 is an addition of the values in D6 and E6 ; what is the value in H6 ?

If you really want the difference between the time today , and the time yesterday , then the times in D6 or E6 need to be used , as in :

=E2 + D6 - ( B2 + B6 )

which will give : 33 hours.

=E2 + E6 - ( B2 + B6 )

will give : 46 hours.

Narayan

 

[inddon]

Hi Don ,

I am confused as to what exactly you wish to do.

1. You have 3 time values in cells B6 , D6 and E6 ; are these times as in 10:00 AM or 3:00 PM or are they hours as in 7:30 signifying 7 hours and 30 minutes ?

2. The value in G6 is an addition of the values in D6 and E6 ; what is the value in H6 ?

If you really want the difference between the time today , and the time yesterday , then the times in D6 or E6 need to be used , as in :

=E2 + D6 - ( B2 + B6 )

which will give : 33 hours.

=E2 + E6 - ( B2 + B6 )

will give : 46 hours.

Narayan

Hi Narayan,

Thank you for your response.

In the start I was also confused.

The totals are a combination of different format cells:
Cell G6= Format time [h]:mm (eg. 33:00 hours)
Cell H6= Format Number 00.00 (eg 02.00)

(1): I have attached the file 'Total all times', with explanation for your reference.

Now these two cells needs to be added and should give 35:00 hours

(2): The other sample file, which also has problems in adding all the times also gives wrong results. Attached file 'Sample adding all times - problem'

How can this be achieved using formulas to get right results?.

Please advise and help.

Thanks & regards
Don

 

[NARAYANK991]

Hi Don ,

That is the problem with formatting ! It conceals the true value in the cell.

The first total of working hours was showing 08:00 , but the real value was something else.

The value of 00:00 in Q6 was not just a time value of 00:00 ; it had a date component also , which was adding to the hours.

Now , it's OK.

Narayan

 

[NARAYANK991]

Hi Don ,

Regarding your first file , I am still confused.

You say that the worker started working at 01:00 AM on a particular day , and then took a break ; when the break was taken is not specified.

Work was resumed at 10:00 AM , and ended at 11:00 PM on the same day.

If the worker again started at 01:00 AM the next day , we can calculate the rest time between 11:00 PM and 01:00 AM because both of them are times.

However , we cannot calculate the total time worked , since the time at which the break was taken is not known.

See this file.

Narayan

 

[inddon]

Hi Don ,

That is the problem with formatting ! It conceals the true value in the cell.

The first total of working hours was showing 08:00 , but the real value was something else.

The value of 00:00 in Q6 was not just a time value of 00:00 ; it had a date component also , which was adding to the hours.

Now , it's OK.

Narayan

Wow!, Thanks Narayan.

Could you please tell me the steps you applied to get 60:00:00.

In the actual workbook, I changed the format to [h]:mm:ss, it still gives me the same incorrect result.
[Added]: I got it, I have applied your approach of formulas for WRK columns as well, and it does fine.


Also in that workbook, there are columns with label headers RST-1. Their format is number 00.00. I would like to convert it into time format [h]:mm. Could you please advise how could this be achieved?


Thanks again for your time.

Regards
Don

 

[NARAYANK991]

Hi Don ,

See this file.

The format is now hh:mm , and all the formulae in these cells have a divisor of 24.

Narayan

 

[inddon]

Hi Don ,

Regarding your first file , I am still confused.

You say that the worker started working at 01:00 AM on a particular day , and then took a break ; when the break was taken is not specified.

Work was resumed at 10:00 AM , and ended at 11:00 PM on the same day.

If the worker again started at 01:00 AM the next day , we can calculate the rest time between 11:00 PM and 01:00 AM because both of them are times.

However , we cannot calculate the total time worked , since the time at which the break was taken is not known.

See this file.

Narayan

Hello Narayan,

Thank you for the sample solution. The dates are not included in this calculation. It is assumed, Time 1 is always 'Morning' and Time 3 is always 'Night'. The resulting difference of time to be known is the gap between Night and Morning for the same row (i.e. night - morning, 23:00 - 01:00= 2 hours)

I have included the MOD functions to achieve it, it works okay. Displays result on H6.


The problem lies in J6, where it totals up cells G6 (time format - 33:00) + H6 (number format - 02.00). How can this be converted into time format to get 35:00?

Please advise

Thanks again for your giving me the time.

Regards
Don

 

[inddon]

Hi Don ,

See this file.

The format is now hh:mm , and all the formulae in these cells have a divisor of 24.

Narayan

Lovely and wonderful. Thanks a lot.

I am going through your updated formulas and see the difference.

I have learned another new Excel thing from you.


Regards
Don

 

[NARAYANK991]

Hi Don ,

That is what has got me confused ; when we are dealing with time values , please note :

1. The difference of two times ( time values in hh:mm:ss AM / PM or time values in Hours ) results in time values in Hours. For instance 15:00:00 ( 3:00:00 PM ) - 11:00:00 ( 11:00:00 AM ) will result in 4 Hours. Similarly 7:00:00 Hours - 3:00:00 Hours will result in 4:00:00 Hours.

Also , 15:00:00 ( 3:00:00 PM ) - 4:00:00 Hours will result in 11:00:00 ( 11:00:00 AM ).

2. Two time values can be added only if at least one of them is in Hours. Thus , 15:00:00 ( 3:00:00 PM ) + 4:00:00 Hours will result in 19:00:00 ( 7:00:00 PM ). Similarly 01:00:00 ( 01:00:00 AM ) + 13:00:00 Hours will result in 14:00:00 ( 2:00:00 PM ).

Adding 10:00:00 AM and 23:00:00 ( 11:00:00 PM ) to get 33:00:00 Hours is not correct.

Adding 10:00:00 Hours and 23:00:00 Hours to get 33:00:00 Hours is perfectly correct.

Narayan

 

[inddon]

Hi Don ,

That is what has got me confused ; when we are dealing with time values , please note :

1. The difference of two times ( time values in hh:mm:ss AM / PM or time values in Hours ) results in time values in Hours. For instance 15:00:00 ( 3:00:00 PM ) - 11:00:00 ( 11:00:00 AM ) will result in 4 Hours. Similarly 7:00:00 Hours - 3:00:00 Hours will result in 4:00:00 Hours.

2. Two time values can be added only if at least one of them is in Hours. Thus , 15:00:00 ( 3:00:00 PM ) + 4:00:00 Hours will result in 19:00:00 ( 7:00:00 PM ). Similarly 01:00:00 ( 01:00:00 AM ) + 13:00:00 Hours will result in 14:00:00 ( 2:00:00 PM ).

Adding 10:00:00 AM and 23:00:00 ( 11:00:00 PM ) to get 33:00:00 Hours is not correct.

Adding 10:00:00 Hours and 23:00:00 Hours to get 33:00:00 Hours is perfectly correct.

Narayan

Hi Narayan,

I didn't know about this at all in Excel. Now I know why it was not adding it up. Thank you very much for sharing the above valuable information.

I will go through your revised formulas and understand the concept and apply it accordingly in the actual workbook.

Thanks again for your prompt help as usual.

May I ask you to please tag this post as [SOLVED]

Regards
Don