What else do you want to learn in Excel? Let me know below in the comments!
@Askjerry Жыл бұрын
Thank you. However... if they start at 11:00 PM to 7:00 AM on the swing shift... the formula fails. How do you handle that? (I'm guessing you have to do a full date at that time... or some more math to determine it changed days.)
@mahdipi55129 ай бұрын
@tinytechnicaltutorials What if i wanted to know how many hours they worked, after the regular 8 hours?
@rahimelhajdk9 ай бұрын
Thanks for the video, but you didn't mention if the second time is next day, then how to calculate the time difference?
@kyrelrosli9 ай бұрын
Hi, what if the format is dd/mm/yy, hh:mm:ss AM/PM? For example I have 11/15/23, 9:02:43 AM and 11/15/23, 5:04:17 PM, I want to calculate the time duration in hours, how do I do that? I follow exactly like 5:01 and it still wouldn't work. Please help!
@TinyTechnicalTutorials9 ай бұрын
Hi Kyrel! 👋 Maybe this will help? stackoverflow.com/questions/22187581/calculate-the-difference-between-two-dates-and-time-on-excel
@DeityMari29 күн бұрын
How do you convert duration to decimals when it spans across days? If someone works 12 hours across two days, I get 12:00 but need to convert to decimal (12) so I could use it in another calculation.
@uppimage11 ай бұрын
I am just about to watch the tutorial. I see that you actually respond to comments, so I will start my thought process (and delete it if the video can answer). My goals/needs: I need to enter Start Time, End Time, Total Hours (decimal form) and hourly rate. I am stumped when converting to decimal form. Start time A1 (10:30) End time B1 (16:00) Find the difference and have it swapped to decimal format C1 (3.5) My formula is listing the difference as 6.30 (instead of 6.5) At this point, I can take the decimal and multiply by the hourly wage and get my correct total. THE ISSUE: I do NOT want to enter the full time with colon AM/PM. For example, 9:30 AM I enter as 930, but I want it to appear as 9:30 AM. I am comfortable with entering military time, but without the colon, and I would like it to appear in the AM/PM. I would enter 1630, and have it appear as 4:30 PM. It think it is because of this NOT entering colons that rendered inoperable the formulas I have found on most of the tutorials. When I followed your instructions, it worked, but not when I enter time as above. This is the glitch I would like to solve. Once I get this workflow set up, things will go so much more smoothly for the rest of the process! Thank you in advance.
@TinyTechnicalTutorials11 ай бұрын
Hey @uppimage! 👋 I do respond to comments, but have been a bit slow this week! 😊 Were you able to sort this out? You should be able to specify the format for the time under Format Cells-->Custom-->Type. Here's a write-up that might help? www.causal.app/excel/entering-or-importing-times-without-colons
@user-ml3mb1be5i8 ай бұрын
What if the start and End time sits in same cell. viz. 06:00 PM - 08:23 PM, and output should be 2:23. Is it possible?
@TinyTechnicalTutorials8 ай бұрын
Hmmm...it really complicates things if the times are in the same cell. 😊 I found one example that might be what you need here: superuser.com/questions/1770821/time-difference-when-times-are-in-one-cell. You'd basically have to split the data at the hyphen, but it gets pretty messy. Hopefully that helps!
@caraghabrielledorsett54605 ай бұрын
Is there a way to get the military time if it’s not showing up when you click format cells and time?
@TinyTechnicalTutorials4 ай бұрын
Hi Cara! 👋 Hmmmm...so when you do Format Cells-->Time, on the right-hand side, there's nothing like a "13:30"? Bizarre! You could try doing a custom format. Here are some examples of that: www.ablebits.com/office-addins-blog/show-time-over-24-hours-excel
@AnnistonTiger Жыл бұрын
When I calculate this it always returns a #value error. My spreadsheet has arrival and leave time in the same column. So A1 shows arrive and A2 shows leave. If I calculate =(A2-A1) I get a #value error and no calculation. Column A is date and time and it can't be changed. How can get rid of the #value error?
@TinyTechnicalTutorials Жыл бұрын
Hi HarleyRider! Hmmm...not totally sure why you'd be getting a value error. Feel free to send the spreadsheet to info@tinytechnicaltutorials.com and I can take a look. 😊
@khulekanindaba65064 ай бұрын
Hi TTT, how would I calculate the same if I had the dates and times in different cells?
@TinyTechnicalTutorials4 ай бұрын
Hi Khulekani! 👋 So sorry for the slow response! Were you able to figure this out?
@jhoc2x22 күн бұрын
I wish there the scenario where it goes beyond midnight... like 9PM to 5AM schedule
@bennyblanka39253 ай бұрын
Hello great video! For the first example, how do you total the number of hours worked in the week?
@TinyTechnicalTutorials2 ай бұрын
Hi Benny! 👋 Sorry for such a slow response! Were you able to figure this out?
@zerandervax1046 Жыл бұрын
Bonza mate, worked like a charm, thanks muchfully :0)
@TinyTechnicalTutorials Жыл бұрын
Thanks heaps, mate! 😄
@shabirfk Жыл бұрын
Thank you. Very precise tutorial. What if I need the result in days, hours & minutes?
@TinyTechnicalTutorials Жыл бұрын
Hi Shabir! Thanks for watching! 😊 Here's an article that should help you get days, hours and minutes: exceljet.net/formulas/get-days-hours-and-minutes-between-dates.
@SorryPogiLang272 ай бұрын
TIPS: For computing time, you will need to use ABS function especially when the result is negative so it can be converted to positive because it will only show number sign"#" in the cell if the time value is negative
@TinyTechnicalTutorials2 ай бұрын
Ahhhh...great point! Thanks for posting this tip! 🤓🔥
@BananaTheFancy Жыл бұрын
Thank you so much
@TinyTechnicalTutorials Жыл бұрын
You bet! Thanks for watching! 😊
@camayffoo Жыл бұрын
how do you calculate the total timing worked ? base on the above example
@TinyTechnicalTutorials Жыл бұрын
Hey fairyangel fairyangel! I think you're asking how to get the total time worked for the five days in my example (like a weekly total time)? Sure! You'll want to be using the "Decimal" example (see 03:13 in the video). Then put your mouse in cell C9, and on the top ribbon, "Home" tab, click "AutoSum" (should be towards the right). That should automatically detect that you want to sum up the five cells above, and should give you the total. Just make sure that C9 is using a "Number" for the format. You can check this by right-clicking on the cell, then click "Format Cells." On the "Number" tab, make sure the "Category" (on the left) is "Number." Hope that helps! :)
@Rahul83487 Жыл бұрын
What if in one cell it has10 am - 8 pm what is the Excel formula to calculate this hour
@TinyTechnicalTutorials Жыл бұрын
Hi Sashi! 😊 You mean the single cell has the text "10 am - 8 pm"? If that's the case, that cell would probably be treated as text, and you wouldn't be able to do a calculation on it. But let me know if I'm misunderstanding!
@user-xi1dk3gw3kАй бұрын
Thank you so much.
@TinyTechnicalTutorialsАй бұрын
You're welcome! Thanks for watching! 🙏🤓🌟
@camayffoo Жыл бұрын
how to you calculate the total timing pend ? base on the above example
@TinyTechnicalTutorials Жыл бұрын
Replied in your other comment. :)
@mahdipi55129 ай бұрын
What if i wanted to know how many hours they worked, after the regular 8 hours?
@TinyTechnicalTutorials9 ай бұрын
Hi Mahdi! 👋 Ooh, interesting! There are several ways you could do this. I've created a screenshot of one way here: drive.google.com/file/d/1Z8sxW6vQLi4S-4q1jdyeq5cUcXyoZEHY/view?usp=sharing. Hopefully it makes sense! 🤓
@ritesh0480 Жыл бұрын
How to calculate difference of 20:00 i.e 8PM to 8:00 i.e 8AM?
@TinyTechnicalTutorials Жыл бұрын
Hi Ritesh! Sorry for the delay...if you're still looking for an answer, you should be able to update the formula to be (24-start time on day 1)+(end time on day 2). This will give you the total hours on the first day (24 total hours minus the start time) plus the total hours on the second day. Hope that makes sense!
@chadclemens3107 Жыл бұрын
how can i write a formula that only compares 2 cells in a given row that will have 4 values? For example, cell B1 has a value of 6 and C1 has a value of 2. the "Difference cell" will total C1-B1 = -4. then when i add a value of 3 into cell D1 of 3, the "difference cell" will total D1-C1 = 1. then when i add a value of 3 into cell E1, the "difference cell" will total E1-D1 = 0. Hope this makes sense lol
@TinyTechnicalTutorials Жыл бұрын
Hi Chad! I'm following this part: "cell B1 has a value of 6 and C1 has a value of 2. the "Difference cell" will total C1-B1 = -4." But then got a little confused. :) Can you maybe try explaining in a different way?
@chadclemens3107 Жыл бұрын
Ok I'll try lol B1 = 6, C1 = 2. Difference cell (F1) = -4 D1 & E1 do not have values yet Then when I put a value of 3 into D1, the difference cell will subtract D1 from C1, which would be -1. Then when I put a value of 3 into cell E1, the difference cell will subtract E1 from D1, which would be 0. Each time I put a value in a cell, I want the difference cell to subtract the new value from the previous cell. I hope this helps!
@TinyTechnicalTutorials Жыл бұрын
Hey Chad! Thanks for the updated explanation! That's super helpful. I've tried a few things, and found one that I think (??) is what you need, but I haven't thought through all possible scenarios where it might not work. -If D1 AND E1 are empty, then difference is C1-B1 -Otherwise, check if E1 is empty -If it is, then difference is C1-D1 -If it’s NOT, then difference is D1-E1 Here's the formula for the "difference cell" (F1): =IF(AND(ISBLANK(D1), ISBLANK(E1)), C1-B1, IF(ISBLANK(E1), C1-D1, D1-E1)) Hope that will at least get you started! :)
@chadclemens3107 Жыл бұрын
@@TinyTechnicalTutorials you are amazing, thanks!
@TinyTechnicalTutorials Жыл бұрын
You bet! Good luck!
@JamshedAhmed Жыл бұрын
Masha Allah (WoW)
@TinyTechnicalTutorials Жыл бұрын
Thanks so much! 😎🌟🙏
@vasiliibogdan1291 Жыл бұрын
@ 5:33 does not give me difference. Please tell how to get difference in hours between long dates. What you have on screen. I copied - does not work!
@TinyTechnicalTutorials Жыл бұрын
Hi Vasilii! 👋 What are your values for Travel Start and Travel End?
@vasiliibogdan1291 Жыл бұрын
@@TinyTechnicalTutorials nvmd my Long Date fromat was messed up! got it working! Thanks though!
@sakshiparwani24067 ай бұрын
Thank you
@TinyTechnicalTutorials7 ай бұрын
You're welcome! Thanks for watching! 🙏🤓🌟
@kelseyg2012 Жыл бұрын
what about the night shift that works past midnight?
@TinyTechnicalTutorials Жыл бұрын
Oooh, good one! 😊 For that, you can try 24-(StartTime-EndTime). But that would only work for the night shift times. You'd have to use the formula from the video for day shift folks (EndTime-StartTime). Hope that helps! Thanks for watching! 🙏🌟🤓
@charneclarke5959 ай бұрын
Use the "IF" formula
@umbnmbu4679 Жыл бұрын
lol but what about substracting a smaller number AM from a larger number PM, isnt this a thing to speak about or show example also?? like Start time: 9 PM and End time 2 AM... how do you do this?
@TinyTechnicalTutorials Жыл бұрын
Oooh, an example I hadn't thought about! LOL! 😁 I haven't tried it for all the different formats, but on military time and the standard AM/PM time, you can do =(24 - StartTime) + EndTime. Let me know if that works!
@umbnmbu4679 Жыл бұрын
@@TinyTechnicalTutorials You can use the function Mod(). e.g. if you have from A1(11:00 pm) to B1 (3:00 am), then Mod(B1-A1,1) 1 being the divisor
@TinyTechnicalTutorials Жыл бұрын
Ahhhh, okay! Didn't know that. Thanks for letting me know! 😃
@user-by3uk4eq2f6 ай бұрын
@@TinyTechnicalTutorials How to deal with negative numbers? I am converting all check ins from CST to PST some check ins are at 1:00 am when converting goes into negative and ###### because the result goes back into a yesterday at 11:00 pm. Please help thank you.
@TinyTechnicalTutorials6 ай бұрын
Hi Erika! 👋 I think you could update the formula to be (24-start time on day 1)+(end time on day 2). This will give you the total hours on the first day (24 total hours minus the start time) plus the total hours on the second day. Does that work?
@zaldaim70439 ай бұрын
How do you deduct 1hr lunch time..eg. 12.00 to 1.00pm or 13.00
@TinyTechnicalTutorials9 ай бұрын
Hi Zaldaim! 👋 Sorry for the slow response! If you haven't already figured it out, maybe this will help? www.extendoffice.com/documents/excel/3610-excel-calculate-hours-worked-minus-lunch.html
@BasicCnnr11 ай бұрын
Hey TTT you really sound like my favourite band KKK, but seriously though it sounds like i heard someone from a certain website of black and orange