Home |
Search |
Today's Posts |
|
#1
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
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
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to change 000-000-0000 phone format to (000)000-0000 ???? | Excel Worksheet Functions | |||
show in text format the difference between 2 dates | Excel Discussion (Misc queries) | |||
HOURS DIFFERENCE IN TIME BETWEEN DIFFERENT DATES AND TIMES | Excel Worksheet Functions | |||
Converting from time format to decimal and figuring the difference | Excel Discussion (Misc queries) | |||
Phone number format from 000.000.0000 to (000)000-0000 | Excel Discussion (Misc queries) |