|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 14th, 2009, 04:56 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! 
|
|
#2
April 14th, 2009, 05:19 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
|
#3
April 15th, 2009, 02:40 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
