Working with Time in Microsoft Excel | Time Functions and Conversion
HTML-код
- Опубликовано: 22 июл 2024
- Do you need to add or subtract time in Excel? Some time calculations are easy, but depending on how the time is entered, you may need to use the TIME function or formula to add or subtract time. For example, if you have 9am in B2, and in cell C2, you have 3, for three hours, you can't add them with B2+C2 and get the correct answer without the TIME function.
**********
Chapters:
**********
00:00 Intro to chapters
01:00 TIME function
02:56 Add two-time numbers
4:10 Add time format and number together
5:25 Subtract time
6:20 Total time over 24 hours
7:30 Subtract time over midnight
8:20 Time as a decimal
Excel file used in this video
drive.google.com/file/d/10zn-...
Blog post
chrismenardtraining.com/post/...
**********
TIME Function
**********
TIME(hour, minute, second)
The TIME function syntax has the following arguments:
Hour Required. A number from 0 (zero) to 32767 representing the hour. Any value greater than 23 will be divided by 24, and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM.
Minute Required. A number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM.
Second Required. A number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM
=B2+TIME(3,0,0)
If B2 contains 9am, the function will return at 12pm. It adds, 3 hours to 9am.
**********
Websites:
**********
Chris Menard's website: chrismenardtraining.com
Cristian's RUclips Channel: / @graphicious
And make sure you subscribe to my channel!
- EQUIPMENT USED --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ RUclips keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week! 
Хобби
Thank you! You just saved me SO much time! I needed to show an arrival time 30 minutes before a scheduled time and I wasn't sure how to make that happen - you made it so simple! Thanks again!
Glad to help. Thanks, Stacy.
Thank you Chris! easy and clear explain!
You're welcome!
Thanks a lot Sir it solved my problem with in few seconds as your video was on point and not wasting any time
Man, u're a lifesaver.
God bless you
Thank you, Ayman.
Thank you….i just started with Excel….i needed this lesson . 👍
You are welcome!
Very well explained, U R exceptional Chris.
Thank you kindly! I appreciate it, Ahmed.
Thanks so much…you are the only one that I understand.
Glad to hear that! Thanks, Marlene.
Hello. How can we combine csv files in excel in simpler format without vba or any macro?
Excellent Chris.
Many thanks!
thanks you, you just saved me So much time, bless you sir.
You're welcome!
Great video! How do you subtract 15 minutes from 1:02:00 am? Thanks
Is there a formula for cycle time (Taking the current time - display time in a cell)?
Do you have a video for how to do times for running workout time for different distances?
Any suggestions to covert for example: 1m 30s to 1.5 or 4m 30s to 4.5 ???? Please
Hi Chris, Error says: *File is no longer available for download.* Please re check the download link. Thanks a lot.
What formula can I use to determine if a start time/end time is in AM or PM if it just shows the military time without the AM/PM indicator?
Chris M, Love your Video: Thanks for showing some COOOOOoooooL Tricks. Current Time = " Ctl Shift ; " THANK YOU!!!
You're welcome!
Hi Chris' - I've seen this solution but I sill cant get it to work. I assume it may be a problem with the Version of Excel I'm using being the Chrome in house Google version? Adding up a list of times with a total at the end that doesn't recycle the clock continues to elude me...
Hi, if I want to know the difference time between 2 cells is it 15 min or the difference is 30 min what will be the correct function to use
My blog post with the file is here. chrismenardtraining.com/post/working-with-time-in-excel-time-function I have over 200 Excel video. Here is my playlist with the most recent videos at the top. ruclips.net/p/PLsYjn_fAfG42S1JmTFr7B5eTQRvm5w3sF
Hi, Its a great video.
I have one question how can i calculate the return time with respect to assign time and our working hours is 8 per day.
Thanks in advanced
Great one. 🎉
Thanks 😁
I have time written as 00:30:09 in my rows, but appears as 12:30:09 on the formula bar. How do i fix this?
This appears normally on my personal system, but differently as explained above on my work desktop
do you have something about regular time and overtime??
Can Excel calculate the duration of time over many days? For example, I start my well pump on June 1 at 10 AM and turn it off on June 7 at 3PM. Is there a formula that gives me the amount of hours and minutes the pump was on?
Hi Chris, since you seem to be an Excel Guru, i've a question that you may be able to help.
is there a formula that add to someting while keeping a previous calculation?
Let me explain what i'm looking for:
Let's say i have in cell A1 a value "100,00 hr" & in cell A2 "1,50 hr" (using decimal values with suffix, not hours / time)
(meaning 1.50 hr instead of 1:30:00)
I want A1 to sum the added value every time i add something on A2.
Let's say monday i add 1,50 hr to 100,00 hr so A1 should be "101,50 hr"
Tuesday i write 2,00 hr in cell A2, i want it to sum up in A1 ( "103,50 hr"
Wednesday i write 5,00 hr in cell A2, i want it to keep summing in A1 ( = 108,50 hr)
First of all, is it possible to do this? and if it is, what would be the formula?
To be even more spécific i'm gonna use it on Google Sheets, hopefully , if there's a formula on Excel, Google Sheets will deal with it.
Thanks a million Chris.
What’s the formula for a timer for a break aid begin time and return time
sir plz help and slove , =now() current time in cell and manualy time another cell both are similar how to define vlookup , validation , true /false etc
Hey Chris. I want to add cumulative hours in Excel. I am able to do it with [h] Format upto 4 digit in hours. What to do for more than that.
Like I have 30000:20 hours and minutes and would like to add say another 100 hours to this.
Hi, very smart. How can I display a result in a cell that has a formula displaying time, to display minutes?
Hi Chris, I am your fan from Pakistan, I work in HR Department, I am making employee's time report and checking his actual time in and out Vs scheduled time in and out, I am stuck in the below situation: Cell G7 has scheduled time out value as 20:30, where as cell H7 has end time value as 20:00, if I apply this formula "=IF(H7
Ticket create date : 05/01/2024 09:30 AM, Ticket closing date: 07/01/2024 12:30 PM, I want the total duration but after 09 pm to 09 am i dont want to count the hours, whats the excel formula. please help.
Nice presentation
Thanks a lot
TNX.
Glad to help.
how about very important topik subtracting time past midnight - he ?
Thanksful
Thank you.
Google drive link don't work.
nice and good job. but please put all excel practiced files in the description
blog post bottom of the page. chrismenardtraining.com/post/working-with-time-in-excel-time-function
Hello,
How about, i have the START DATE in column A, START TIME in column B, END DATE in column C and END TIME in column D. I need to calculate the Time Elapsed using those 4 data. Please help! Thank you
Will this works? drive.google.com/file/d/1_sQAe7kw1ht9OHYYMkXSHFLf55KrAain/view?usp=drivesdk
@@ChrisMenardTraining It's working, thanks a lot!
Hi Chris, new fan here. I'm trying to impute hours and minutes to create a graph of how long I spend time on social media. If I put in 1:45 in a cell it keeps trying to read it as 1:45 am. How can I keep hours and minutes without realating to the time of day.
@Chris, I have the same question.
nice and good job. but please put all excel practiced files in the description....
bottom of the blog post chrismenardtraining.com/post/working-with-time-in-excel-time-function
Nice 1
Thank you!
Thanks
Thank you so much!
@@ChrisMenardTraining You are worth every penny!!! Thank you,
Great Video, i need help create a formula to convert dollar to time, (eg. $100 to Hours and each $1=1Minute, so $100 looking for end result to be 1:40 minutes)
Divide by 1440 and format as time. Here is a screenshot. drive.google.com/file/d/1PK09AzABsEuQB-D0hw1OlZxFq10KUng1/view?usp=drivesdk
@@ChrisMenardTraining
I knew had to be something easy
Hello, If I have to work 12 hours a day (6:00 AM to 6:00 PM).
And the company doesn't need me to punch in and out 30 minutes for lunch during the day.
What is the formula or how do I need to do it in Excel?
Thank you in advance.
I combined two screenshots showing the formula and then the format I created. drive.google.com/file/d/17W06EQh3_50Qt4hEkWFqrJZ3TcEQzU_7/view?usp=drivesdk
@@ChrisMenardTraining
- Wow, It looks so simple.
- However, Looks like I have to punch out and punch in during the day in order to get the total of 11:30 (11 hours and 30 minutes). In my case, I do not have to punch in and out for lunch at all.
- One other thing, does this formula work with Excel 2007?
- Because after I try with this formula all I see is ####### no matter what I change or select in the custom, type, h:mm option.
Here I include a screen shot so you can see.
Thank you very much for your time.
(By the way. How I can attach a photo?)
Private Investigator Here - Q. How to add time in 24 HR clock? When it goes over Midnight on the same day? and then deduct 1/2 hr break?
That works fine if you work past midnight within the same 24 hour period. but let’s say you wanted to find out how much time off you had. As an example you got off work at 5:28PM on Tuesday the fifth and you came back to work at 9:03AM on the 12th. How much time were you off?
Would this work. Image attached drive.google.com/file/d/1KaCBzlHAackHuFfPJQDgef_5UIDy3UpE/view?usp=drivesdk
@@ChrisMenardTraining thank you very much that looks like it works, but I also found something a little more simple. You can format the time in AM or PM or in 24 hour format. You must then enter the date and time even though the cell may be formatted to only show the time. The calculation is the later time minus the earlier time. The format of the cell you do the calculation has got to be set up in special format. I found. [h]:mm works best.
Need to increase a cell by one every new day
Excel is absolute crap with date and time. I just dont know why they stick to these obscure methods. E.g. counting all time/date with a number originating from 1900 ( having to convert everything to an insanely long decimal which then gets auto-rounded and you lose accuracy when converting back) or when it auto-converts your input to date and when you change the format to normal numbers, your input is corrupted.
Thanks for providing an insight and tips!
Poor visibility of text
I'm not sure what you mean. The text on the video?