Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates and times in excel
3/6Hello.
I have a formula that calculates date and time differences. The formula works when the same day in inputted in the two fields, but not with two different days (returns #NAME? . I am using the following format to input information (yyyy/mm/dd hh:mm) with my present formatting is comes out as (3/6/06 1:30 PM). the formula I am working with. My DayEnd and DayStart is 8:00 AM - 8:00 PM. Any ideas? =IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((J7-INT(J7))- (I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+ (24*DayEnd-(24*(I7-INT(I7)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates and times in excel
http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours
-- Regards, Tom Ogilvy "marquis de montrose" wrote in message ... 3/6Hello. I have a formula that calculates date and time differences. The formula works when the same day in inputted in the two fields, but not with two different days (returns #NAME? . I am using the following format to input information (yyyy/mm/dd hh:mm) with my present formatting is comes out as (3/6/06 1:30 PM). the formula I am working with. My DayEnd and DayStart is 8:00 AM - 8:00 PM. Any ideas? =IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(IN T(I7)=INT(J7),ROUND(24*(J7-I7),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((J7-INT(J7))- (I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+ (24*DayEnd-(24*(I7-INT(I7)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates and times in excel
That looks like a formula from my site. Do you have all the
required names defined? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "marquis de montrose" wrote in message ... 3/6Hello. I have a formula that calculates date and time differences. The formula works when the same day in inputted in the two fields, but not with two different days (returns #NAME? . I am using the following format to input information (yyyy/mm/dd hh:mm) with my present formatting is comes out as (3/6/06 1:30 PM). the formula I am working with. My DayEnd and DayStart is 8:00 AM - 8:00 PM. Any ideas? =IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((J7-INT(J7))- (I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+ (24*DayEnd-(24*(I7-INT(I7)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates and times in excel
Hello Chip.
It did come from your site. As a matter of fact, your site has taught me a lot about this functionality, and I thank you for that. Yes. I beleive that all of my required names are defined. Would it come out with an answer like that if they were not, being that it works for the same day but not two different days. I will check the names again and make sure that this is not the case. I guess I am trying to rule out things like formatting that may affect this. I will get back to you tomorrow about the names. Thanks again. "Chip Pearson" wrote: That looks like a formula from my site. Do you have all the required names defined? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "marquis de montrose" wrote in message ... 3/6Hello. I have a formula that calculates date and time differences. The formula works when the same day in inputted in the two fields, but not with two different days (returns #NAME? . I am using the following format to input information (yyyy/mm/dd hh:mm) with my present formatting is comes out as (3/6/06 1:30 PM). the formula I am working with. My DayEnd and DayStart is 8:00 AM - 8:00 PM. Any ideas? =IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((J7-INT(J7))- (I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+ (24*DayEnd-(24*(I7-INT(I7)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates and times in excel
I will add. I don't think that the names for StartDT and EndDT are the proper
case. I will get back to you. "Chip Pearson" wrote: That looks like a formula from my site. Do you have all the required names defined? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "marquis de montrose" wrote in message ... 3/6Hello. I have a formula that calculates date and time differences. The formula works when the same day in inputted in the two fields, but not with two different days (returns #NAME? . I am using the following format to input information (yyyy/mm/dd hh:mm) with my present formatting is comes out as (3/6/06 1:30 PM). the formula I am working with. My DayEnd and DayStart is 8:00 AM - 8:00 PM. Any ideas? =IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((J7-INT(J7))- (I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+ (24*DayEnd-(24*(I7-INT(I7)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates and times in excel
You may be able to use an easier formula. Can your start and end times/dates be outside working hours? If not then you could use =(NETWORKDAYS(A1,B1,holidays)-1)*("20:00"-"08:00")+MOD(B1,1)-MOD(A1,1) custom format as [h]"hrs" m"mins" where A1 contains start time/date, B1 end time/date and holidays is a named range containing holiday dates. -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=519526 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
dates and times in excel
Hi Chip.
I apologize for my lack of e-mail etiquette. I should have posted your website along with my question. I have yet to really sit down and be able to analyze all of the names I created. I will keep you posted as to my success (if you wish). Thanks again. "Chip Pearson" wrote: That looks like a formula from my site. Do you have all the required names defined? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "marquis de montrose" wrote in message ... 3/6Hello. I have a formula that calculates date and time differences. The formula works when the same day in inputted in the two fields, but not with two different days (returns #NAME? . I am using the following format to input information (yyyy/mm/dd hh:mm) with my present formatting is comes out as (3/6/06 1:30 PM). the formula I am working with. My DayEnd and DayStart is 8:00 AM - 8:00 PM. Any ideas? =IF(AND(INT(I7)=INT(J7),NOT(ISNA(MATCH(INT(I7),Hol idayList,0)))),0,ABS(IF(INT(I7)=INT(J7),ROUND(24*( J7-I7),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((J7-INT(J7))- (I7-INT(I7)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(J7-INT(J7)))-24*DayStart)+ (24*DayEnd-(24*(I7-INT(I7)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate duration between two dates and times in excel? | Excel Discussion (Misc queries) | |||
Calculate times between dates and times in Excel | Excel Discussion (Misc queries) | |||
history of dates/times an excel worksheet was opened? | Excel Discussion (Misc queries) | |||
formula in excel to track times between two different dates ? | Excel Worksheet Functions | |||
Count times between 2 times and 2 dates | Excel Programming |