I need a formula to add 42 hours (1.75 days) to a cell that contains a date and time minus weekends. I'm currnetly using =A2+B2+2*(WEEKDAY(A2)=6)but when I enter 6/16/11 7:07 AM in A2 and want to add 1.75 in B2 business days it shows 6/18/2011 1:07 AM but I need it to show 6/20/2011 1:07 AM based on a 24 hour clock
The formula seems to work for all start days of the week except Thurdays, not sure why, any help would be wonderful.
Would this work for you? Just guessing.
Something like =A2+B2+(WEEKDAY(A2)=6)+2
Loppy
WOW, Loppy, that seemed to fix the issue I was having, TY so much for the quick reply, I had no idea it was going to be that easy. Thank you.
I don't think that will work in all circumstances though - what if A2 is Friday at 07:00 and B2 is 1.75 - that formula will give you next Wednesday at 01:00 rather than next Tuesday.
You could use WORKDAY like this
=WORKDAY(A2,B2+MOD(A2,1))+MOD(A2+B2,1)
where A2 is any weekday date/time
Audere est facere
Actually, now looking at it further, Does your Friday come out correct now?
There ya go, Daddylonglegs got it. Very cool. Yeah, when I looked at mine after the fact, Friday's didn't work. heh.
NM, I understand it now. Thanks again.
Lop
Last edited by Loppy; 06-16-2011 at 03:49 PM.
Thank you Daddylonglegs, your formula seems to work correctly for any day, Thank you so much for the help.Good day to everone.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks