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.
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
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.
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.
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
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.
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
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 😭😭
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"),
![BLACK BAG - Official Trailer [HD] - Only in Theaters March 14](http://i.ytimg.com/vi/Du0Xp8WX_7I/mqdefault.jpg)
I'll keep making them!
Excel fun is addictive and helpful at work too!
Thanks!
Thank you for your donation! It helps me to keep making Excel videos : )
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!!!
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!
*Thank you sir. Don't stop teaching. God bless always.*
Dear loverpeace33,
I am glad that they videos are helpful!
I'll keep making them!
--excelisfun
Dear chirag1883,
I am glad you like them!
--excelisfun
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
This might work for a Time Number formatting:
[h]:mm
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.
It really is magic. I love it!! It works!!
how to calculate it when on date and time was already used concatnate funkcion
I GOT WHICH I NEED.
THANKS
Wow! Thank you so much for this.
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 ignore weekends sat and Sunday if any in between, while calculation ?
how to calculate the half of an hour
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.
Thanks you sir, but say how about if i wanted to include the minutes?
Shouldn't it be 21 days 4 hours...
try:
=INT(C13)-INT(C12)&" days and "&TEXT(C13-C12,"hh:mm:ss")&" hours"
EXCELlent Mike.
is helpful video. worked
It work for me in 2024......
Nice trick.
Thanks
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
Nice
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.
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.
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
i think 484 should be subtracted by 21*24 to get hours so it will be 21 days and 20 hours
What about the 33 mins
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 😭😭
21 multiplied by 24 = 504
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"),
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 have book and DVD:
mrexcel [dot] com/slayingdragonsbundle.html
Hi
none of it works if you can't figure out how to enter a 'square-bracket"..