General info and problems about timeslots sum

[blak24]

Hello everybody,
I just started working with Excel, and I have a problem, well, mostly an info for now. I have some timeslots to record (it's a long story, unfortunately I cannot tell more for other reasons), every single day of every month. The problem is that I have to sum these timeslots (for example, Monday, from 1.00PM to 1.30PM, from 5.00PM to 5.25PM, total is 55 minutes, Tuesday, from [...]). Is there a way to do the sum of these timeslots every day in a completely automatic way? (so without changing page at the bottom of the project). What I was asking to myself is that, if I use a single page for a month, I have to manually add the formula used to a specific cell at the end of the day. So I was thinking that the only way to do so is to create a page for every day of a month, but it's quite uncomfortable... And, is there a way to find the minutes between a time interval and then do the sum of the results? In the case mentioned above, for example, first I have to calculate how many minutes elapsed between 1.00PM and 1.30PM (30), then I have to do the same for the second timeslot (25) and then I have to sum the two results (30+25=55 minutes which is the total of the day). I tried to explain at my best, but I understand that it could be not so clear :D I'm attaching my "idea", which basically contains what I have in my mind... Hope it helps!

THANK YOU IN ADVANCE

P.S: If there is something wrong with this discussion, please let me know... Thanks again!

EXAMPLE.xls

[Melvinrobb]

Would the attached work?

[judgeh59]

Is there a multitude of time slots per day or maximum of 2? Most people will tell you to keep your data on one worksheet. This is usually a good idea, so having a worksheet for every day/week/month/year is not a really good idea....it actually sounds like a pivot table will be your friend....also, how is you data layed out in the worksheet?...IE: colA has start time and ColB has end time.

Ernest

[blak24]

Would the attached work?

Almost perfect! I'm adapting it with a few changes (adding an "if" formula to convert hours in minutes if result is >= 1) and it works great! Thank you so much!

Is there a multitude of time slots per day or maximum of 2? Most people will tell you to keep your data on one worksheet. This is usually a good idea, so having a worksheet for every day/week/month/year is not a really good idea....it actually sounds like a pivot table will be your friend....also, how is you data layed out in the worksheet?...IE: colA has start time and ColB has end time.

Ernest

Well I have more than 2 time slots per day, but I've just added a number of time slots which I will not reach in any case... So it's good now. For your other questions, just download the attachment which was made by Melvinrobb, it explains well which problems I had. Anyway... what is a pivot table?


EDIT: Last question: after calculating the total amount in minutes, is there a way to calculate the number of hours (in the format hh:mm)? Because if I divide the number of minutes by 60, I obtain the number of hours in the format hh:XX where XX is a decimal number, and not minutes... To calculate minutes I would have to do something else. For example: total time is 150 minutes, divided by 60 is 2.5, 2 hours and 0,5 hours, so 0,5*60=30 minutes. SO, total is 2:30 (hh:mm) Is there a way to do so? Thank you!

[Melvinrobb]

You shouldn't need to change the formula I used in my attached file. confirm your formatting is the same, and you should have what you want. In the file I attached previously, M5 is 1:58, or 1 hour and 58 minutes. Is that not what you want?

[blak24]

You shouldn't need to change the formula I used in my attached file. confirm your formatting is the same, and you should have what you want. In the file I attached previously, M5 is 1:58, or 1 hour and 58 minutes. Is that not what you want?

Yes but I think there is something wrong after the first two rows. If I add 15.50 (start) and 15.55 (end) in the 2nd row it works good, 0.05, but if I do the same in the 3rd (or 4th etc..) row the total is 21.22 (????) Don't know why... So i used an "if" formula to convert that total in minutes if the result is above 1 (so if the result is above 1 hour ---> 60 minutes, if it's below 1 it remain as it is). In this way it works, but I should have also a total in hours. If it's not possible, having the final total (highlighted in green) both in hours and in minutes, it's ok as it is now... It's just a curiosity

[Kevin UK]

Hi blak24

See the attached, I have changed the times to proper times and then your totals as decimal time!

[Melvinrobb]

Still not sure what the issue is.
Please indicate where the issue is in the file I have uploaded, stating what it is currently showing, and what you want it to show.

[blak24]

Ok guys, I uploaded another file where I explain my last question... Just double-click on X13 to show the full content. Thank you again for your support
Cartel1.xlsx

[Melvinrobb]

The reason you are having the issue is because you are entering the times with a "." and you should be using a ":" to separate the hour and minutes.

[blak24]

Ok guys, problem solved I had just to divide the result in minutes by 1440, and then select the appropriate cell format (in this case HH.MM). Thank you again!