Hi,
Attached is a time sheet which has shifts that can either start and finish in the same day or at times start one day and finish in the next.
My problem is that I want to total the hours worked in a shift/week and when a shift starts in one day and finishes in the next then I get an error in the formula.
Is there a more clever formula that I can use that will over come this?
Thanks,
Adam.
Try this in R2:
=IF(B2="Day Off",0,IF(B2="Hol",0,IF(C2<B2,(C2+24)-B2,B2-C2)))
sorry I have hasd to amend this several times - this should be correct now
edvwvw
Last edited by edvwvw; 10-11-2010 at 08:43 AM.
try this and let me know if this is what you are looking for
Hi I have a spreadsheet that does what you require
PM me and I will send you a copy
Cheers
Toonies
@Toonies, if you have something to share please do so on the Board directly.
Sharing of this nature is after all the general ExcelForum.com ethos
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
=IF(OR(B2={"Day Off","Hol"}),0,MOD(C2-B2,1))
is a shorter way, and allows you to add other text simply without nesting ifs
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!
You could also only apply the hours calculation if both times are present, i.e.
=IF(COUNT(B2,C2)=2,MOD(C2-B2,1))
Audere est facere
I didnt think that would work because of the merged cells!
Nice!
(as a side note =IFERROR(MOD(C2-B2,1),0), is even shorter in 2007/2010)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks