LinkBack URL
About LinkBacks
Hi all!
Here I have a formula:
=IF(AND(E4<>"",X25<>"",Q25<>"",S25<>""),INT(E4+X25-Q25-S25)&" Days "&TEXT(E4+X25-Q25-S25,"h:mm"),"")
But I need to combine it with MOD function. How?
Thanks!
Hi all!
Here I have a formula:
=IF(AND(E4<>"",X25<>"",Q25<>"",S25<>""),INT(E4+X25-Q25-S25)&" Days "&TEXT(E4+X25-Q25-S25,"h:mm"),"")
But I need to combine it with MOD function. How?
Thanks!
I am assuming you need the MOD function to get the time?
e.g
=IF(AND(E4<>"",X25<>"",Q25<>"",S25<>""),INT(E4+X25-Q25-S25)&" Days "&TEXT(MOD(E4+X25-Q25-S25,1),"h:mm"),"")
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Follow the link. This is thread about my problem.
http://www.excelforum.com/excel-gene...t-7-00-am.html
It shows negatives to me
examples of what you have and what you want would be nice.
Here you are.. I`ve built you an example what I need. Thanks a lot!
Forgot attachment... Sorry...
I still don't understand what the formula you want is for?
What would also be the result you expect with your sample and why?
Task started:
05-May 22:00
Task finished:
05-May 02:00
The result I want - 4 hrs
Task started:
05-May 05:00
Task finished:
06-May 08:00
The result I want - 3 hrs.
Just where am I working, date changes not at midnight, but at 7:00 am.
=IF(AND(E4<>"",X25<>"",Q25<>"",S25<>""),TEXT(E4+X25-Q25-S25,"dd /da/y/s h:mm"),"")
/d /y /s are there because dys are formatting commands, the / tells the format to take it as a character!
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
Try this
It worked for me
Start Date End Date Result Formulae 05 May 2010 22:00 05 May 2010 02:00 4:00 ="24:00"-MOD(A2,1)+MOD(B2,1) 05 May 2010 05:00 06 May 2010 08:00 3:00 ="24:00"-MOD(A3,1)+MOD(B3,1)
Last edited by contaminated; 05-28-2010 at 03:35 PM.
Люди, питающие благие намерения, как раз и становятся чудовищами.
Regards, ?Born in USSR?
Vusal M Dadashev
Baku, Azerbaijan
Ok. Forget the topic of this thread. Imagine, day starts 7:00 am. Today is 28-May, tomorrow at 7:00 am will be 29-May. So, i have a paperwork in front of me, saying task started 27-May at 23:00 and finished 27-May at 02:00. Actually, task duration is 3 hours. This is the point where MOD function helps me. But when duration is bigger than 24 hours, formula (and formatting) says 0 days and whatever hours. How to make excel think that 24 h day runs 7:00 am to 7:00 am???
Sorry, you`ve posted while I typed
All right. There is one BUT. My dates and times are in separate cells. In this case what am I suppose to do with this formula?
the best way I think
More about Date & TimesStart Date Start Time End Date End Time Result Formula 05 May 2010 22:00 05 May 2010 2:00 04:00 ="24:00"-MOD(A2+B2,1)+MOD(C2+D2,1) 05 May 2010 5:00 05 Jun 2010 8:00 03:00 ="24:00"-MOD(A3+B3,1)+MOD(C3+D3,1)
Last edited by contaminated; 05-28-2010 at 03:49 PM.
It doen`t work for me. It calculates time but not days. There some tasks whitch take 2 days or more. The MOD function does not deal with days. Try to put start date 03-May any time and finish date try 06-May any time. Sure enough, won`t work. How to deal with it?
.
sorry didn't see post # 12
What the output shild be if
StartDate is 12 May 2010
StartTime is 23:00
EndDtae is 30 May
EndTime is 03:00
?????????
It should say 28 hrs
Ooops! I have counted as the end date was 13-May. But you asked for 30-May? The maximum duration at my work is up to 4 days.
StartDate is 12 May 2010
StartTime is 23:00
EndDtae is 13 May
EndTime is 03:00
OUTPUT IS 28
am I right????
Yes. 28 hrs.
Sorry but I need a little bit clarification
Explain the logic please once agai
How do you count?
I have made an example for you. This should explain you what I really want. Have a look at it.
thanks for clarification
I'l take a look at this tommorow
It's 2:41 AM (((
Hi,
How about this?
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
No. That easy I can do myself. Have a look whats happening in example 2. See attached...
Assuming start date in A2, start time in B2, end date in C2 and end time in D2 then you can use this formula to give total hours, e.g. 28:00
=C2-A2+MOD(D2-"7:00",1)-MOD(B2-"7:00",1)
format as [h]:mm
or if you want the result as 1 day(s) 4:00
then try like this
=INT(C2-A2-(MOD(B2-"7:00",1)>MOD(D2-"7:00",1)))&" day(s) "&TEXT(1+D2-B2,"h:mm")
Finally... Finally??? Finally!!!
Thanks a ton folks! Everyone who helped me in the past, and hopefully will help in a future. Thanks!
It works!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks