Our Excel training videos on YouTube cover formulas, functions and VBA. Useful for beginners as well as advanced learners. New upload every Thursday.
For details you can visit our website:
http://www.familycomputerclub.com
Microsoft Excel makes calculations with 'time' very easy. So if you wish to calculate the monthly payments for your employees you need to have a start time, an end time and the payments per hour. Whether the person works during day-shifts or night-shifts, Excel can help make the calculations easy and automatic. Just ensure that you enter time in the format x:y:z, where x = hours, y=minutes and z=seconds if you wish to have seconds in your data else just use x:y, i. e. hours and minutes separated by ':' (colon). If you want enter time as 'am' or 'pm' just add a 'p' after the time. If you don't write anything after the hours, minutes and seconds MS-Excel assumes it is 'am'. If you use the '24-hour' clock, Excel has no problems with 'am' or 'pm'.
Get the book Excel 2016 Power Programming with VBA: http://amzn.to/2kDP35V
If you are from India you can get this book here: http://amzn.to/2jzJGqU

Text Comments (119)
## Would you like to comment?

ajit pgims (3 days ago)

Very nice sir

Java Team (1 month ago)

Thankkkksssss

sanjay talekar (3 months ago)

hindi me bool

Dinesh Kumar Takyar (3 months ago)

yahaan jaa: https://www.youtube.com/user/MSExcelinHindi

Md.Bachchu Mia (5 months ago)

Thank you...........

Jamal Said (6 months ago)

Thinks sar

Soumya Roy (6 months ago)

How can we get negative time with different indication?

Sudhir Rawal (10 months ago)

Sir , my simple question if i had a time slot i.e 18/00 to 00/00 and my night calc from 22/00 - 00/00 so i want to put an value in cell -a1 =18/00 and b1 =00/00 and results will calculated by automatically to c1 =22/00 and d1 =00/00
How could i do plz help

Lynx Team (3 months ago)

the best way not to feel lost with time-slots and calculations is to use time-tracking software like tmetric or harvest or tsheets

MARWAN TAHER (11 months ago)

THANK U VERY VERY MUCH GOD BLESS U MAN

Shira Marcus (11 months ago)

Thank you for this video. it was very helpful!

Zain Khalid (11 months ago)

Hi,
it is a challenge for any excel user, I need to calculate working hours and minutes but I do not want to enter the time in time format. i want to enter 10-16 instead of 10am - 4pm or similar . can you make a formula which converts start time and end time from the same cell and then calculate the time ???????????????????????

Mohanraj Gct (1 year ago)

sir i have an equation for example (overtime amount /overtime hours)=per hours rate. my problem overtime is time format. if i divided my overtime amount, my result wrong. can you help me how fix this problem?

Dinesh Kumar Takyar (1 year ago)

This link will help: https://www.exceltrainingvideos.com/payment-calculations-based-on-timestamps-in-excel/

kapoor5302 (1 year ago)

Hello sir
I am beginner in excel.. plz help me to make an excel where a person log-in time is there and it will calculate that what will be his logout time as per CST & IST time zone (since i work at night shift US process) to complete 9 hours in office for the day..... plz help sir vry urgent.....

Damaris Figueroa (1 year ago)

Thanks!!!!!!!!!!!!!!!

Matt Silva (1 year ago)

How would I use this to add 3 time periods in a day? say 6:30-10:30, 14:00-18:00, & 22:00-2:00. I understand and have used your formula for one time period but cant figure out the rest. I have up to 3 time periods per day and want to make sure it calculates all three appropriately regardless of the time of day like the last one mentioned about (22:00-2:00). This is what I have in place: =IF(D6>E6,1+E6,E6)-D6 , but need to add F to G & H to I. Thank you

Dinesh Kumar Takyar (1 year ago)

Search https://www.exceltrainingvideos.com or http://www.familycomputerclub.com

anju av (1 year ago)

thank you sir

Happy Heart (1 year ago)

I just created the greatest spreadsheet of all time.. lol Thank you

MD TOUHIDUZZAMAN (1 year ago)

I solved my problem. Thanks a lot.

Dinesh Kumar Takyar (1 year ago)

Congrats!

sagar suri (1 year ago)

sir i have problem in exporting data from sale sheet to invoice copy

Anne Drew (1 year ago)

why is become a 43.75 instead 43.15? thanks for answering

Palash Chakrabarty (1 year ago)

Thank You Mr Kumar i was face problem when i calculation day one to next day morning it come to error now i am solve the issue . but when working continue 2 day how is it work
is it if + 2 day

franzel dizon (1 year ago)

This is very helpful! Thank you so much!

NS Farm (1 year ago)

Thank You . you saved my day

Mon Mi (1 year ago)

Great video, thank you!!!!

DHARMA MOHAN (1 year ago)

Thank you

Shabs M (1 year ago)

thank u so much.....so easy to understand

Gina Davis (2 years ago)

This was an amazing video. Simple instructions and easy to understand. It was exactly what I was looking for. THANK YOU SO MUCH!!!!

Lawson Bushu (2 years ago)

THANK YOU SOO MUCH...SOO HELPFUL

vaibhav more (2 years ago)

how to count total month hr count
ps help

Jagan Nathan (2 years ago)

I want millisecond format and I don't have hh:mm:ss.000 in my excel 2013 format window. Is there any add-in for that? pls help.

Loreen Maravilla (2 years ago)

Thank you!

pavan kumar (2 years ago)

How could time value .75?

rio evi (2 years ago)

awesome, thank you so much

forsakenAdik (2 years ago)

waw! so very helpful, thanks for sharing!!!

John jr (2 years ago)

excellent.....one more question..how do we calculate by having a lunch break say for an hour ?

Dinesh Kumar Takyar (2 years ago)

Have a look at this link: http://www.familycomputerclub.com/excel/using-time-functions-in-excel.html

KcoRdiKjr (2 years ago)

Thanks.Very helpful.

Nirosha Priyadarshani (2 years ago)

Thank you very much Sir, you explained very clearly. its very useful. thanks once again.

Anton Siva (2 years ago)

Thank you very helpful

Dinesh Kumar Takyar (2 years ago)

+Anton Siva Thanks for watching.

Ryan A. Flournoy (2 years ago)

This quick video was the answer to my problem. Thank you!

Abinash Kumar (2 years ago)

Hi Dinesh,
I have small query i want to add exp of employees in YY::DD format. For Example if i have to find out total exp of an employee which includes previous exp and current experience. You can consider 2.10 (years n months)as previous exp and current comp exp is 2.11 (years n months) total should be 5.9 (years n months) but when try to sum i am not getting the same value i tried using year and month functions. Can you help me without using any macros.

Abinash Kumar (2 years ago)

+Dinesh Kumar Takyar Thanks! But the video also has the same content, which did not answer my query. Please share me your email ID.

Dinesh Kumar Takyar (2 years ago)

+Abinash Kumar This link might help: http://www.exceltrainingvideos.com/date-functions/
You might also like to checkout the web-links at the bottom of the article.

John Taxpayer (2 years ago)

Hello Dinesh. Quick question. I'm trying to simplify our work schedule, and the data entry needed for my employees, and this is what I have. Presently, we are in Time 13:30 format for the Time In, Time Out, Duration(Out-In) cells. Here is the current punch sequence Time In 8:00 Time Out 16:45 Hours Worked 8:45. How do I change the format in all three cells, so we only have to enter 0800 1645 and only the duration(meaning hours worked cell) shows up as 8:45 with a colon?

PARAMOUNT INTERIORS (2 years ago)

33:18:00
45:02:00
01:13:11
01:53:59
01:31:16
37:01:00
how can i add the above in excel ......
plese help urgent

PARAMOUNT INTERIORS (2 years ago)

can you please show me a solution on how to do this addition in excel

PARAMOUNT INTERIORS (2 years ago)

+Dinesh Kumar Takyar yes i know but it can be assumed in our line of business which is uber and ola

Dinesh Kumar Takyar (2 years ago)

+PARAMOUNT INTERIORS How would you know when it is minutes and when it is hours?

PARAMOUNT INTERIORS (2 years ago)

+Dinesh Kumar Takyar
Dear DINESH SIR,,
This is the format in which i receive data of the duration of time a car is rode. I am confused on how to add this up in excel hence need your help.
33:18:00 (33 min 18 secs)
45:02:00 (45 min 2 secs)
01:13:11 (1 hour 13 min 11 secs)
01:53:59 (1 hour 53 min 59 secs)
01:31:16 (1 hour 31 min 16 secs)
37:01:00 (37 min 1 sec)
l
please help urgent

Dinesh Kumar Takyar (2 years ago)

+PARAMOUNT INTERIORS These links will help:
http://www.exceltrainingvideos.com/calculate-difference-in-hours-between-two-date-time-values/
http://www.exceltrainingvideos.com/payment-calculations-based-on-timestamps-in-excel/

Eric Lenardt (2 years ago)

Dinesh you rock! Thank you.

egwpisteuw (2 years ago)

Thanks

Felcia Lin (2 years ago)

Thank you very much for this video, Sir. You helped me with my assignments.

Abbad Ali (2 years ago)

Thanks man

Los Pescadores (2 years ago)

That was perfect

Vincent Nathan (3 years ago)

Kindly Explain why you did not convert 75 mins in 1 hour 15 mins kindly re-upload this video with the Auto Sum time option but thanks on the first step

Kareem Mahgoub (3 years ago)

Thanks Mr.Dinesh Kumar Takyar
Have a great day always
Best,
Kareem

Dinesh Kumar Takyar (3 years ago)

+Kareem Mahgoub Thank you!

Control P Advertising (3 years ago)

i cant do it ( the formula you typed contain an error
pls help

David Cole (3 years ago)

Is there a way to automatically take out 30 minutes for lunch if the employee works more than 6 hours in a day?

Prakash Vyas (3 years ago)

in your example the actual total is 43:45 hrs. whiloe it display 43.75 (perhaps it will be in decimal ) but what to do if we want it to get total in hr:mm"ss

Margeret Hawthorne (3 years ago)

still don't understand

Nowfar alm (3 years ago)

tnx 2 u

Tenesse Yanez (3 years ago)

Hi Mr. Takyar,
I would like to ask how to computed Total Time needed work less Total Time rendered. Take for example,
TOTAL HOURS NEEDED TO WORK l TOTAL TIME RENDERED l Lacking Hours
3:14 l 4:00 l ?
3:01 l 4:00 l ?
Looking forward to have an immediate response :) Godbless!

kousar tariq (3 years ago)

Thank you soo Much

Jennifer Roya (3 years ago)

Thanks so much Dinesh, I have been really struggling with this. Your explanation was simple and easy to follow, what's more importantly it WORKS!!!

Houssem Kan (3 years ago)

Thanks

Standard Trevor (3 years ago)

Thank you so much for this.

Tek D (3 years ago)

How we add if we have lets say: (Hr:Min) but want sum in Hours?
A1: 12:00 =12hr, 0Min
A2: 08:00 =8hr, 0min
A3: 00:10 =0hr,10min
A4: 00:20 =0hr, 20min
Thanks,
Tek

Tek D (3 years ago)

+Dinesh Kumar Takyar
Thanks sir. And one more question but not related with it: If I am using multiple page in Userform and have Command-Button Called "Next". If I click Next Button on page 1 it should take me to page 2. Is it possible? If yes what would be the VBA coding for cmdNext.
Thanks,
Tek

Dinesh Kumar Takyar (3 years ago)

+Tek D
Method A
=sum(A1:A4)
Format the cell with the formula to h:mm
Result: 20:30
Method B:
=sum(A1:A4)*24
Format the cell as a number with two decimal places.
Result: 20.50
http://www.exceltrainingvideos.com
https://youtu.be/_d-UGO0JSnk

wastedlove25 (3 years ago)

nice tutorial..thanks!help me alot

Kullakaln G. (3 years ago)

Thank you so much!

Mukesh Punalkar (3 years ago)

NICE VIDEO

Santosh Sawant (3 years ago)

I want time in excel as per system clock

kolkata police rugby nabin (3 years ago)

Thank you very much

Marcelino Torrecilla (3 years ago)

Thanks Dinesh, This was very useful

sagar jagtap (3 years ago)

Very Nice...............Thank You Sir...........:)

Vorname (3 years ago)

Nice video; nevertheless two tips and tricks: 1:) instead of an if-formula better use =mod ((endtime minus beginning time;1)). 2. Instead of using number formats try custom format and choose the one that is displayed [h];mm;ss :) works better and is easier:)

Vorname (3 years ago)

+Dinesh Kumar Takyar was a pleasure to me;)

Dinesh Kumar Takyar (3 years ago)

+kathrin9674 Excellent ideas!

tomiasorensen (3 years ago)

Very nice, but. My team can be simple. If I enter 00:00 until 00:00 I get zero hours. How do I get it to take that into account?

10521577A (3 years ago)

I can't hear anything.

Ashish Bansal (4 years ago)

Hi...
I am Ashish, working in a company as a supervisor...I need your help pls...
I want to keep track on some of my team members in order to gaze how much time they spent on their system.... specialy on excel...
Their work is to listen recorded calls and to evaluate the call on some defined parameters...
I want something in excel who can tell me that at what time my team member started to evaluate the first call and what time he/she finished and saved overall summary, findings and feedback on excel
Same for 2nd, 3rd 4th.....10th, 20th call
I want to know how much time my team member takes to evaluate a call...
Can u help me pls...
My num: 9818866044
Email: [email protected]

Litz93Enzy (4 years ago)

if i type 60 how do i make that in to 1 automatically. e.g. 60min as displaying 1hr.
as you can see in you video the total hours 43.75. so if its in time hours and min way it should be 44.15 (as in 44hrs and 15min). i hope you understand what im trying to say and hope you can help me with that. thank.

kissibrown (4 years ago)

thank you so much.

Sahil Faizi (4 years ago)

Hi I would like to ask you if i add another cell break time so how can we min the break time from the total time thank you.

Deepan Subramanian (4 years ago)

good thanks

Mohammad Khairul Islam (4 years ago)

thank you sir

johny english (4 years ago)

Awesome video...just had a question, how would you find average time minutes and seconds.

Dinesh Kumar Takyar (4 years ago)

+johny english Have you tried to use the average function? Moreover, time needs to be entered with an a or p for am and pm respectively. Otherwise Excel assumes the time is am.

johny english (4 years ago)

+Dinesh Kumar Takyar an average for the following time. 5:50,6:60,3:30,2:50

Dinesh Kumar Takyar (4 years ago)

+johny english Can you give an example?

Kaviyarasan V (4 years ago)

The exact solution, Very Nice, Thanks a lot

Wayne Johnson (4 years ago)

got to the point,THANKS

Sue Grundy (4 years ago)

thank you for this video. It really helped me

Rdfs Bzs (4 years ago)

And I did find this useful, thank you.

John Kenneth Selidio (4 years ago)

how about if work with a BREAK TIME? how will i get the calculate of that... thanks

John Kenneth Selidio (4 years ago)

thank you so mucvh

Dinesh Kumar Takyar (4 years ago)

Check this video out:http://www.exceltrainingvideos.com/time-functions/

Dinesh Kumar Takyar (4 years ago)

If you have a break time like teat-time or lunch-break, then enter the data into a cell and take it also into account.

Nikhil C Saha (4 years ago)

you are so great teacher. thank you so much

chandran slevaraj S (5 years ago)

Thank you very much!

Fazrullah Jaini (5 years ago)

Hi, can anyone help me. How to calculate hours between 2 or 3 dates exclude the non working hours? lets say 1/20/2013 11:00PM (case come in after working hours) -> 1/21/2013 10:30 AM (case assist in working hours). Non working hours will be 10:00 PM until 8:00 AM. answer will be 2.5 hours as case come in after working hours, so will start counting from 8AM. How to minus the non working hours. I need the formula.
Pls help me. ;(

Genavie Solano (5 years ago)

Thank you very much!

WolvaiThreenaija Muslim Wolf, Robo, and Azmarai (5 years ago)

Thank you very much

khamis Al Mazrouei (5 years ago)

smart. easy and basic... thanks dear.. no need for MOD.

Bernardo Pulido (5 years ago)

I really like this it's nice and simple, I am wondering if you could put "day off" at the Start time and still do the calculation, because it's giving #value! error. If possible what would be the new formula?

sonu rawal (5 years ago)

its very nice

Steven Whiting (5 years ago)

How about if you have 30mins or 1hr breaks? Where it doesn't matter when the break was taken, they'll just either be 30mins or 1hr breaks per day.

Steven Whiting (5 years ago)

Nice, clear, short and easy to understand. Just wanted something basic for on my phone, this was what I wanted. Thanks.

Anthony Paul (5 years ago)

HE Has such a beautiful voice and it is easily understood..........its marvelous

gedrick sagum (5 years ago)

im very sorry i cant understand what ur saying coz ur speech was not that clear.. i have one question what is the purpose of the variable 1 ??? i analyze it but it confuse me

Bader alkhmshy (6 years ago)

Hi, how can the total hour be 43:75? while the hour =60 min

Join YouTube for a free account, or sign in if you are already a member.

© 2019 Sunset west hollywood hotel

Der OBI Online-Shop bietet Ihnen verschiedene Waschmaschinentypen unterschiedlicher Markenhersteller wie zum Beispiel Siemens oder Gorenje. Diese stehen für Qualität und Zuverlässigkeit. Mit einer Online-Bestellung können Sie sich jetzt selbst davon überzeugen. Sie haben die Wahl zwischen Front- und Topladern. Die Maschinen sind in verschiedenen Energieeffizienz- und Preisklassen zu Hause. So ist sicher für jeden Geschmack und Geldbeutel die richtige Maschine vorhanden. Wäschetrockner in großer Auswahl. Auch die Wäschetrockner aus dem OBI Online-Shop sind Produkte unterschiedlicher anerkannter Hersteller wie beispielsweise Bosch oder Gorenje. Hier können Sie sich ebenfalls zwischen Front- und Topladern entscheiden. Toplader sind besonders bei mangelndem Platz praktisch. In für Trockner typischen Räumen, wie Kellern, ist genau das häufig der Fall. Durch die große Vielfalt ist sicher für alle Anforderungen das richtige Gerät verfügbar – bequem per Online-Bestellung. * Die angegebenen Verfügbarkeiten geben die Verfügbarkeit des unter „Mein Markt" ausgewählten OBI Marktes wieder. Soweit der Artikel auch online bestellbar ist, gilt der angegebene Preis verbindlich für die Online Bestellung. Der tatsächliche Preis des unter „mein Markt“ ausgewählten OBI Marktes kann unter Umständen davon abweichen. Alle Preisangaben in EUR inkl. gesetzl. MwSt. und bei Online Bestellungen ggf. zuzüglich Versandkosten. UVP= unverbindliche Preisempfehlung des Herstellers.