Hi Folks,
Could someone help me with the below, please? it is pretty easy but I can't seem to have it work when I tested it.
In which format should I change “the Extra session time/per” so I can add it later to the session time?
I need column D values to be calculated based on column B values, and then I will be able to add all to get the value of the Total time
Total time = session time + Extra session time/hr + Prep time + Break time
Many thanks in advance.
Time format per hour
-
- NewLounger
- Posts: 22
- Joined: 13 Feb 2024, 09:41
Time format per hour
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Time format per hour
I'd either enter the extra session time as a number of mimutes, for example 10 for 10 minutes/hour, or as a time value in format h:mm or hh:mm, for example 0:20 for 20 minutes/hour.
I'd do the same for the break time.
Enter 0 or leave blank for no extra time / no break time.
If you enter them a number of minutes, the formula in F2 would be =SUM(B2:C2, D2:E2/1440)
1440 is the number of minutes in a day.
If you enter them as time values, the formula in F2 is simply =SUM(B2:E2)
I'd do the same for the break time.
Enter 0 or leave blank for no extra time / no break time.
If you enter them a number of minutes, the formula in F2 would be =SUM(B2:C2, D2:E2/1440)
1440 is the number of minutes in a day.
If you enter them as time values, the formula in F2 is simply =SUM(B2:E2)
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 13 Feb 2024, 09:41
Re: Time format per hour
HI Mr. HanV,
I have followed your advice but there is still something wrong. I am not sure if I did it wrong or if the formula is not populating the correct total
So for example in the updated uploaded sheet, I have changed column D to the time formate
But with using the formaule I am getting 3:10 in F1 while it should be 3:40
10 min per hour (D1) from he session time (B1) = 40 min
so the total time will be 3:00 (session time) + 0:10 (prep time) + 0:30 ( extra time per session hour time) + 0:00 ( break time)
I still cant figure out how to change column D to calculate time per session hour.
Hope I explained it well.
Thanks for your usual help and support.
I have followed your advice but there is still something wrong. I am not sure if I did it wrong or if the formula is not populating the correct total
So for example in the updated uploaded sheet, I have changed column D to the time formate
But with using the formaule I am getting 3:10 in F1 while it should be 3:40
10 min per hour (D1) from he session time (B1) = 40 min
so the total time will be 3:00 (session time) + 0:10 (prep time) + 0:30 ( extra time per session hour time) + 0:00 ( break time)
I still cant figure out how to change column D to calculate time per session hour.
Hope I explained it well.
Thanks for your usual help and support.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78788
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Time format per hour
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 22
- Joined: 13 Feb 2024, 09:41
Re: Time format per hour
Oh thank you so much. YES, the updated formula gives the correct total time. Much appreciated sir.