Got a question ... This is great - thank you. I do have a question regarding time calculations in Excel. How do you incorporate an unpaid lunch? In your Monday example, the staff member worked 7:00 AM - 4:30 PM. The total hours worked was 9 hours and 30 minutes. However, like most states, you must take a lunch. Most places do a 1 hour lunch break that is unpaid. How would you incorporate this into your equation? Preferably, I would thing an additional column would be necessary as sometimes an employee may take a shorter break or may not get a break. The additional column would be necessary to add in time taken for lunch. Thank you!
about 52 seconds into the video you reference a previous video that I'm now having to hunt all over your page for. and when im reading the description below it says "Watch next video tutorial", BUT WHAT ABOUT THE LINK FOR THE PREVIOUS VIDEO? Not cool
How do format the column for the Hours Worked? I followed the instructions from the previous video on how to format the Start and End Time columns. When I do the calculation for the Hours Worked I am getting AM and not just numbers....
Subtract 1/24 from the total. For example, if you have an hour lunch break when your shift goes over 6 hours, then E3 might look something like =IF(D3-C3
@@edsoncaetanoalbuquerque8214 It's possible I've made a typo above, but without seeing your project in person it's difficult for me to debug your spreadsheet. I would start by checking the cell format to make sure it matches what he's using in the video (start work and end work should be Time format and Hours Worked should be Custom/h:mm;@). I'd also check your inputs (7:00 AM, not 7:00AM). If you're expecting the result to display in decimal format, then you would need to also incorporate the formula alooose posted below and format the Hours Worked cells as Number.
@LaCasaAcademy this was very helpful but something is not right as this formal is not working for 7:00PM to 7:00AM ( its perfectly works for 7am to 7pm )
@@edsoncaetanoalbuquerque8214 Thanks for the additional info. To calculate times when your shift runs from p.m. to a.m., you'll need the info in one of the following videos: ruclips.net/video/pET80HwhVmE/видео.html or ruclips.net/video/hiuY1PGL_I4/видео.html (if you're working with a large amount of data or data for more than one person, it might be faster to set up your spreadsheet to use a power query instead of manually entering and copying all these formulas, but the way shown in this video does work)
Ive done it for a whole month but when I try getting a grand total by either =Cell+cell2=cell3 or by =sub(cell1:cell31) It never gives me a value just displays 00:00:00 . Can someone help me pls?
I need Fx that allows me to range minutes of work hours 0-5 minutes is 0.0, and 6-11 mins is 0.1, and 12-17 minutes is 0.2 and so forth. example 4:00 am to 5:15 am = 1.2 hours worked. this is my current fx =(J14-H14)*24 cell titles = (time out- time in)* 24 this fx works until I have an odd punch in time and or punch out then the numbers can be off by a tenth.
What if I want to calculate working hours from two dates? For example: 03.01.2019 09:40 (J2) and 07.01.2019 09:55(L2) . These two dates have 3 working days so what's the time of solving the ticket during working days? The time of solving the case during the whole period is 96h but including only working days it will be less than 96h. But I don't know how type it in formula in excel.
@@Josegober Try this: =IF((NETWORKDAY(L2;N2)-1-MOD(L2;1)+MOD(N2;1))>0;(NETWORKDAYS(L2;N2)-1-MOD(L2;1)+MOD(N2;1));0) Where L2 is a created_date and time(eg:19.08.2019 11:32:00) and N2 is a resolution_date and time(eg:19.08.2019 23:13:00). Resolution in the third column: 11:41:00 (hh:mm:ss)
The most important part is how to format the numbers and this is not here which makes it useless.
They literally gave you a link to a video that tells you how to do that
=TEXT (D3-C2,"h:mm")
Good
@@lisap631 thanks a lot
The formula that worked for me to yield hours worked as a decimal was:
=HOUR({B2-A2})+MINUTE({B2-A2})/60
Hopefully this is helpful to others!
besttttt
Got a question ...
This is great - thank you.
I do have a question regarding time calculations in Excel. How do you incorporate an unpaid lunch? In your Monday example, the staff member worked 7:00 AM - 4:30 PM. The total hours worked was 9 hours and 30 minutes. However, like most states, you must take a lunch. Most places do a 1 hour lunch break that is unpaid. How would you incorporate this into your equation? Preferably, I would thing an additional column would be necessary as sometimes an employee may take a shorter break or may not get a break. The additional column would be necessary to add in time taken for lunch.
Thank you!
excellent!
now i understand
This is Great for us old guys
This is great I appreciate the short length of the videos and also how you explain the steps concisely, yet with enough detail 💯
Nice tutorial, Thank you
thank you
He's correct - He is frank
ThANK YOU OMG IT WAS THIS SIMPLE
Is there a way to calculate automatically a work hour including time in and out during lunch break ?
Thanks
What do I do if I want to subtract a lunch break from my working hours
ruclips.net/video/8hBb40Gm32g/видео.html
Thanks, sir.
Thank you
about 52 seconds into the video you reference a previous video that I'm now having to hunt all over your page for. and when im reading the description below it says "Watch next video tutorial", BUT WHAT ABOUT THE LINK FOR THE PREVIOUS VIDEO? Not cool
I'm having the same issue ugh
saaaaaame. so annoyed
Thank you for that but, how do you add up the total hours worked for the week?
ruclips.net/video/RMd-zZLqUfM/видео.html
He covers it here
ruclips.net/video/RMd-zZLqUfM/видео.html
THANK YOU
Thanks, it works
Can we convert the time calculated to decimals??
ruclips.net/video/DQh9hOsmp7w/видео.html
Please go through the video for solving your query.
that is simple, but what happened if you clock in at 4 pm and log out at 12:34 what formula do you use?
How do format the column for the Hours Worked? I followed the instructions from the previous video on how to format the Start and End Time columns. When I do the calculation for the Hours Worked I am getting AM and not just numbers....
Format that cell. Once again choose time. Scroll down further and you will see an option that does not contain am or pm.
ruclips.net/video/DQh9hOsmp7w/видео.html
Please go through the video to solve your problem.
Good
Easy straigt to the point thank you so much realy saved me here
It is and always will be pleasure :)
Hi. keeps getting better
Thank you Roderick :)
Everything should have been in one video helpful
Thank you for your feedback :)
good work
Abdul thanks :)
Thx
How to subtract 1 hour break time with the same IF formula
Subtract 1/24 from the total. For example, if you have an hour lunch break when your shift goes over 6 hours, then E3 might look something like =IF(D3-C3
@@LaCasaAcademy something is not right as this formal is not working for 7:00PM to 7:00AM ( its works for 7am to 7pm perfectly)
@@edsoncaetanoalbuquerque8214 It's possible I've made a typo above, but without seeing your project in person it's difficult for me to debug your spreadsheet. I would start by checking the cell format to make sure it matches what he's using in the video (start work and end work should be Time format and Hours Worked should be Custom/h:mm;@). I'd also check your inputs (7:00 AM, not 7:00AM).
If you're expecting the result to display in decimal format, then you would need to also incorporate the formula alooose posted below and format the Hours Worked cells as Number.
@LaCasaAcademy this was very helpful but something is not right as this formal is not working for 7:00PM to 7:00AM ( its perfectly works for 7am to 7pm )
@@edsoncaetanoalbuquerque8214 Thanks for the additional info. To calculate times when your shift runs from p.m. to a.m., you'll need the info in one of the following videos: ruclips.net/video/pET80HwhVmE/видео.html or
ruclips.net/video/hiuY1PGL_I4/видео.html
(if you're working with a large amount of data or data for more than one person, it might be faster to set up your spreadsheet to use a power query instead of manually entering and copying all these formulas, but the way shown in this video does work)
what is formula for total working hours in a month.
Try to complete in single video
The video have been looking for,But when i tried it then it was giving #######.How do i proceed from here, Someone kindly assist ASAP.
why is this not working for me? I have followed each step only to get the ###### result or Value result... any idea why?
Great
Anyone knows how to add an hour lunch?
I tried doing it on the Excel app and it doesnt work😔
The calculation does not work for me when I tried it gives an error #VALUE?!
Any suggestions??
Check out your Time data may be it's text data.
2 THUMB'S UP 👍 👍 GOOD JOB 🥰
Ive done it for a whole month but when I try getting a grand total by either =Cell+cell2=cell3 or by =sub(cell1:cell31) It never gives me a value just displays 00:00:00 . Can someone help me pls?
how do i minus 1hr i'm work at 8am to 5pm and i must not include the 12-1 time
Same enquiry here.
ruclips.net/video/Uts_sL0Hqmw/видео.html
Maybe u can find the answer here.
How to calculate the time 2.30 hrs in to total hrs like 2.5
Where is the next video...
Mans committed multiple labor validations in only one video 💀
I need Fx that allows me to range minutes of work hours 0-5 minutes is 0.0, and 6-11 mins is 0.1, and 12-17 minutes is 0.2 and so forth. example 4:00 am to 5:15 am = 1.2 hours worked. this is my current fx =(J14-H14)*24 cell titles = (time out- time in)* 24 this fx works until I have an odd punch in time and or punch out then the numbers can be off by a tenth.
You have to formulate this manually
If we take one hour four lunch time then company cut our one hour so how we calculate the time?
Why is the format to calculate the hours not here... It's useless na
How to calculate Over ( 6 ball one over )
What if I want to calculate working hours from two dates? For example: 03.01.2019 09:40
(J2)
and 07.01.2019 09:55(L2)
. These two dates have 3 working days so what's the time of solving the ticket during working days? The time of solving the case during the whole period is 96h but including only working days it will be less than 96h. But I don't know how type it in formula in excel.
Hi, any solution on this?
@@Josegober Try this: =IF((NETWORKDAY(L2;N2)-1-MOD(L2;1)+MOD(N2;1))>0;(NETWORKDAYS(L2;N2)-1-MOD(L2;1)+MOD(N2;1));0)
Where L2 is a created_date and time(eg:19.08.2019 11:32:00) and N2 is a resolution_date and time(eg:19.08.2019 23:13:00). Resolution in the third column: 11:41:00
(hh:mm:ss)
In calculating the time difference you need first to convert it to military time
why mine is #value!
How to insert and format time
Need to deduct lunch breaks!!!!
That was not help ful
🥰
This video takes half way in to even start giving any useful information, then leaves out the most crucial parts. Useless.
not helpful
A WASTE OF TIME WATCHING AN INCOMPLETE VIDEO........SORRY TO SAY...
Thanks, sir.