Excel Magic Trick 286: MOD function & Time Calculations (Time For Night Shift, or Negative Time)
Вставка
- Опубліковано 15 бер 2009
- Download Excel Start File: people.highline.edu/mgirvin/Y...
Download Excel Finished File: people.highline.edu/mgirvin/Y...
Full Page With All File Links: people.highline.edu/mgirvin/e...
See how to calculate hours worked for a night shift where workers start at 9 PM and end at 5 AM. See how to use the MOD function and learn the algorithm that MOD uses to calculate. See how to deal with negative Time.
This is an important Time Math / Payroll / Time Sheet Excel solution. It solves the Night Shift time problems in Excel Time Formulas. - Наука та технологія
Dear givingstars,
If it is a matter of entering dates, then use a four digit year when entering. Then Excel knows what to do.
--excelisfun
Dear givingstars,
I just asked this question at the Mr Excel Message Board and Andrew Poulsom suggested this fast and short formula:
=SUBSTITUTE(A1,"/","/19",2)
Now that is a totally cool formula!
--excelisfun
Dear givingstars,
If they are all 1900 and your dates are in the A column, then use this formula:
=DATEVALUE(LEFT(A1,LEN(A1)-2)&"19"&RIGHT(A1,2))
Then Copy, Paste Special Values.
Then Format the cells with Dates Number Format
--excelisfun
Dear givingstars,
Here is still another one!
barri Houdini at the Mr Excel Message Board suggested this formula:
=REPLACE(A1,LEN(A1)-1,0,19)+0
When you have a zero as the third argument, REPLACE inserts characters into a series of characters.
--excelisfun
I am glad that the video helps!!
You are welcome!
Nice!
Thank you for the helpful video!
Dear planiolro ,
You must go and ask barri houdini what it means. He is the Excel master that made that formula at the Mr Excel Massage Board. He knows more than I do about how that formula works. Also, read what he wrote at that post, because he pointed out that because you have different units, depending on what formula you use and assumptions you make, there will always be debate about what the right answer is.
--excelisfun
Awesome tutorial video ExcelIsFun
Thanks so much for your time and effort for making such are useful & powerful tutorial video for us
I learn so much trick & tips & most important keyboard short cut because of you
Your all tutorial are awesome & give them all 5 stars tutorial
Thanks
Dear robw1031,
Thanks for the advice. I will try and make the improvements!
(I always thought that the thunder and hissing sounds was just Excel doing its calculations ;) ).
--excelsifun
I give you No 1 to you who are superb, awesome, enthusiastic, claver Tutor
We (your subscriber) are so lucky who always receive such a valuable tutorial video.
God Bless you excelIsFun. Keep doing good work god is always with you Sir
&
Thanks to mrexcel also who also make such a awesome tutorial also :-)
Many thanks professor for this great tutorials in excel ♥
You are welcome!
Thanks! This was driving me crazy until I found this video.
Dear givingstars,
If you type 1/1/30, Excel puts: 1/1/1930
If you type 1/1/29, Excel puts 1/1/2029
In order to solve this you would need to tell me: if you have a column of dates like mmddyy, how do you know which ones are 1900 and which are 2000?
--excelisfun
Great video. I am having issues with a formula to calculate Night Shift Differential hours
For Tuesday, the employee worked 11pm - 8am (9 hours); took 1 hour lunch 2am - 3am - the NSD hours should be 8 hours (Hours Worked - Lunch), but
I keep getting 9 hours as NSD instead of 8.
I am using this formula =MOD(E37-B37,1)*24-(E37
You could also use... If(c13
How do you include this with a time zone change. I need to do this plus change from EST to CST. What can I do to combine both of these?
Hi, Excel is fun. I have an question about excel magic trick 286. In your notes, you wrote the formula should be 24*MOD(C13-B13+E13-D13,1) if it has a lunch break. However, I calculated the total hours worked below with lunch break by using this fomula, it actually gave me the wrong answer, but I used MOD function only without multplying 24, the answer is correct. Can you explain why? Thank you.
The video is awesome since is explaining the reason of using MOD function.I suppose that the same principle is applied for the calculation of differences between dates when you want to find out the numbers of completed years, months or days.However for me calculation of days is difficult.=C5-MIN(DATE(YEAR(C5),MONTH(C5)-(DAY(C5)< DAY(B5))+{1,0},DAY(B5)*{0,1})).The usage of curly brochettes and MIN function confuses me!Maybe you can explain it in a future video dedicated to this topic.Thank you.
Dear PrincessWithSkills ,
Just go to the playlist for the first 200 Magic Tricks and it is on the 5th or 6th page.
Search for and watch this video to learn how to use the excelisfun channel (including findfing Playlists):
Search For Excel Videos Download Excel Workbooks excelisfun
--excelisfun
How about using ABS function to avoid negative values???
My HR Time Sheet provides the "number" of hh:mm:ss a worker either clocked in late or clocked out early. Ex: on Monday T.R. clocked in late for -1:29:04 and clocked out early for 00:03:14. How to calculate the totals for multiple in/out "counts" of hh:mm:ss? Thank you, Professor Girvin.
Hi - used your formula and it work easily, BUT it give Hours and a percentage of hours (i.e. 1.25) not hours and minutes (1:15) is there away to have the result express in hour:minutes?
How would you do this in military time
I need to calculate overlap shift I hope find video explain this on this great channel
I am not sure what you mean by overlap.
@@excelisfun overlap shift started 16:00 PM till 1:00 AM
@@hazemali382 =MOD(EndTime-StartTime)*24 should work. Just like this this video.
I must be dense... I can't find how to download the workbook...
@dirtylittlecritter yeah. exactly is =mod(1st time - 2nd time,1)
Go to the tarot to find exact reading. Money well spent
You might want to try moving your mic if you can. you could also try a mic stand with a pop screen i.e. you pop your Ps and slur your Ss this is what causes the thunder and hissing sounds in your videos.
I Used To Wonder How MOD For Night Shift Works (Now I Don't)
+Ashay Dwivedi Glad the video helps!
damn!!! that mic boom !!! irritating to the ears.....am using headphones...
Sorry. I made this 13 years ago... 3000 videos later I don't have pop in my more recent ones...
You are welcome!