Difference between 2 dates&time(format 0000), and excludes weekend

Y

YY san.

Hi,
Appreciate if anyone could help me on this. I have in ROW 435
Cell A= 11 Mar 2009 (format is date)
Cell B = 1041 (formatted as special "0000". It is to be entered as military
format, but ignore the colon)
Cell C = 17 Mar 2009(formatred as date)
Cell D = 1759 (formatted as special "0000". It is to be entered as military
format, but ignore the colon)
In Cell E435, I have this as formula to calculate the difference:
=INT(C435+(TIME(LEFT(D435,LEN(D435)-2),RIGHT(D435,2),))-(A435+(TIME(LEFT(B435,LEN(B435)-2),RIGHT(B435,2),))))&"
days
"&TEXT(MOD(C435+(TIME(LEFT(D435,LEN(D435)-2),RIGHT(D435,2),))-(A435+(TIME(LEFT(B435,LEN(B435)-2),RIGHT(B435,2),))),1),"HH:MM")
and the result is "6 days 07:18", which is correct.

However, between this 2 dates, there is a weekend (14 ~ 15 Mar). How can I
exclude this and show the result as "4 days 07:18"??
Not sure how complicated is this formula going to be, but if the result can
be in total hours, I am good too.
Thanks a million!
 
Y

YY san.

I know about thsise 2 functions, just that, I am not sure where to insert them.
Any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Difference between two date/time values 6
Calculate within a time range with only start date and end date 2
Special string convertion to date and time 5
Formula for finding Date difference between 2 dates 4
Calculating difference between times on 2 dates 4
Time Calculations Help 5
Calculate the difference between two times and show dd:hh:mm:ss 7
Special converting of string date&time to date format 4

Top