Excel Magic Trick 299: Date & Time Number - Total Days & Hours Formula
Вставка
- Опубліковано 25 сер 2024
- Use the INT function to calculate total days worked and the TEXT function to calculate total hours worked. See the custom number format for total hours past a 24 hour day (hour time format). See how to use the clipboard to gather copied elements for a formula.
13 years old. I can hardly believe it. This quicky still shines like the sun on a hot summer Sunday afternoon. A very important topic, actually. Thanks for these focused tricks.
You are welcome for the hot summer focused tricks, ZT!!!
*Thank you sir. Don't stop teaching. God bless always.*
I'll keep making them!
Excel fun is addictive and helpful at work too!
Hi there. I am a total excel addict and I am always looking for something new to learn. Great vids and keep up the good work.
You are welcome!
Dear loverpeace33,
I am glad that they videos are helpful!
I'll keep making them!
--excelisfun
It really is magic. I love it!! It works!!
Dear chirag1883,
I am glad you like them!
--excelisfun
Wow! Thank you so much for this.
Thanks!
Thank you for your donation! It helps me to keep making Excel videos : )
EXCELlent Mike.
Nice trick.
Thanks
is helpful video. worked
It work for me in 2024......
Thanks again, very useful video and it solved yet another how to question regarding date/time calculations between dates and times. Do you have your videos on CD or USB we can order? It would save me some time downloading you tube videos.
how to calculate it when on date and time was already used concatnate funkcion
i think 484 should be subtracted by 21*24 to get hours so it will be 21 days and 20 hours
This might work for a Time Number formatting:
[h]:mm
Shouldn't it be 21 days 4 hours...
try:
=INT(C13)-INT(C12)&" days and "&TEXT(C13-C12,"hh:mm:ss")&" hours"
Nice
Hi.
I really need help for this point
for example i have this results
2Years, 4Months,3Days
and i want to sum it together Like
2Years,4Months,3Days+1Years,2Months,2Days.=...?
how to do this ?
Thanks.
How to ignore weekends sat and Sunday if any in between, while calculation ?
I had duration in days in one spreadsheet and I copied it to another spreadsheet but it turned to dates and time. How do I convert back to days?
how to calculate the half of an hour
Thanks you sir, but say how about if i wanted to include the minutes?
Best to do with VBA. I am not good with VBA. Try THE best Excel site (many good VBA people):
mrexcel [dot] com/forum
I am getting an error: #value! ... maybe cause i alredy have a formula on my cells to combine the date and time !
I dont know .. please help me, I'm trying for the last 4 days 😭😭
ive been trying to make a excel spreadsheet to calculate my workers salary. how could i do this prob. work time is from 8:30-5:30 but when u come at 8:46-9am it is 30mins late. 9:01-9:30 it is an hour late and 9:30 onwards considered half day. we are calculating the payroll now how could i extract the late hour just linking the rules in the formula? thanks badly need help
21 multiplied by 24 = 504
I have book and DVD:
mrexcel [dot] com/slayingdragonsbundle.html
As I analyze the trick, the Days & Hours should have been 20 days & 4:00:33hours. I try this function =INT((TEXT(C13-C12,"[h]")+0)/24)&" days & "&TEXT(TEXT(C13-C12,"[h]")-INT((TEXT(C13-C12,"[h]"))),"[h]:mm:ss")&" hours" but it came out like this 20 days & 0:00:00hours. HOPE YOU CAN HELP ME OUT. THANKS A LOT
What about the 33 mins
Mr. Girvin, you make it look so easy! Anyways, have a question.
I want to split say, total number of days elapsed between two dates and list them as "1 month 16 days" for 46 days elapsed. Also, format the data so appropriate singular or plural notation is in effect depending on 1 or more days or months. Thus 109 days would possibly translate into 3 months and 18 days or 11 days would simply be that, 11 days; or exact 2 months (no days) etc.
How to accomplish that? Thanks in advance.
Sir, Is it possible to write date and time function in a single cell ?
Yes, you can type date and time separated by a space or create a date formula and add the time formula.
I seem to be getting weird results with this,are you able to help
07/01/2013 10:41:56 - 07/02/2013 06:29:16 = 31 days 739 hours
format is mm/dd/yyyy hh:mm:ss
Hi
Hi ,thank you very much for your videos I really learned a lot from it. But I had this formula and it does work but I need to subtract the break time from exactly 12pm to 1:30pm . I hope you can help me. Thank you very much.
=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
none of it works if you can't figure out how to enter a 'square-bracket"..