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

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!

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

include Networkdays or Workday function. It will do what you are asking for
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"YY san." wrote:

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!

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

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

include Networkdays or Workday function. It will do what you are asking for
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"YY san." wrote:

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!