Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work days difference between 2 dates.
Please help me. I need to find the time elapsed for workdays only between a
column of dates and times (available in one column or two) and a fixed date and time. I only need hours of a work day. I have gotten so far as to get the whole number of workdays elapsed, but I need more precise data than that. 03373 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work days difference between 2 dates.
I think this will help you:
=(NETWORKDAYS(A1,B1)-2)*8/24+((WEEKDAY(A1,2)<6)*(MAX(0,TIME(17,0,0)-MOD(A1,1))))-((WEEKDAY(A1,2)<6)*(MAX(0,TIME(8,0,0)-MOD(A1,1))))+((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(8,0,0))))-((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(17,0,0)))) I saw it on a post recently. The idea is quite brilliant; I certainly didn't come up with the concept... Anyway, hope it helps you out, Ryan-- -- RyGuy "Jeremy" wrote: Please help me. I need to find the time elapsed for workdays only between a column of dates and times (available in one column or two) and a fixed date and time. I only need hours of a work day. I have gotten so far as to get the whole number of workdays elapsed, but I need more precise data than that. 03373 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work days difference between 2 dates.
Wow. That certainly did something. I am a true novice with excel. What I
would like to know is how did this calculate the difference between times if the only cell I referenced was the date cell? I don;t really understand what the formula did, but I changed it to meet the cells of the sheet that I have and it certainly gave me numbers. "ryguy7272" wrote: I think this will help you: =(NETWORKDAYS(A1,B1)-2)*8/24+((WEEKDAY(A1,2)<6)*(MAX(0,TIME(17,0,0)-MOD(A1,1))))-((WEEKDAY(A1,2)<6)*(MAX(0,TIME(8,0,0)-MOD(A1,1))))+((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(8,0,0))))-((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(17,0,0)))) I saw it on a post recently. The idea is quite brilliant; I certainly didn't come up with the concept... Anyway, hope it helps you out, Ryan-- RyGuy "Jeremy" wrote: Please help me. I need to find the time elapsed for workdays only between a column of dates and times (available in one column or two) and a fixed date and time. I only need hours of a work day. I have gotten so far as to get the whole number of workdays elapsed, but I need more precise data than that. 03373 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work days difference between 2 dates.
This formula assumes an 8 hour working day, between 8:00AM and 5PM with an
hour break. But it also caters for days that start after 8:00AM and/or finish before 5PM, and adds in those hours. I think this is one of my concoctions, I certainly recall a similar one for an 8.5 hour day, but looking at this, I think it may be wrong in not handling the break. What exactly are your criteria? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jeremy" wrote in message ... Wow. That certainly did something. I am a true novice with excel. What I would like to know is how did this calculate the difference between times if the only cell I referenced was the date cell? I don;t really understand what the formula did, but I changed it to meet the cells of the sheet that I have and it certainly gave me numbers. "ryguy7272" wrote: I think this will help you: =(NETWORKDAYS(A1,B1)-2)*8/24+((WEEKDAY(A1,2)<6)*(MAX(0,TIME(17,0,0)-MOD(A1,1))))-((WEEKDAY(A1,2)<6)*(MAX(0,TIME(8,0,0)-MOD(A1,1))))+((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(8,0,0))))-((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(17,0,0)))) I saw it on a post recently. The idea is quite brilliant; I certainly didn't come up with the concept... Anyway, hope it helps you out, Ryan-- RyGuy "Jeremy" wrote: Please help me. I need to find the time elapsed for workdays only between a column of dates and times (available in one column or two) and a fixed date and time. I only need hours of a work day. I have gotten so far as to get the whole number of workdays elapsed, but I need more precise data than that. 03373 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work days difference between 2 dates.
ok, I should clarify a little bit. I run a report that tells me outstanding
orders. Every order that we receive should be processed within 24 hours, excluding weekends. I would like to exclude a list of holidays as well, if possible to make the report more accurate. I do not need to assume a 8 hour work day. The report gathers a list of created dates\times and order numbers. I will be putting in a fixed date\time in one cell and need to know the difference in hours (based on a 24 hour work day, I guess) between the two dates. I can get the date and time in either one cell or two, it pulls from SAP so I can have that configured any way I want. Let me know if you need anything else. "Bob Phillips" wrote: This formula assumes an 8 hour working day, between 8:00AM and 5PM with an hour break. But it also caters for days that start after 8:00AM and/or finish before 5PM, and adds in those hours. I think this is one of my concoctions, I certainly recall a similar one for an 8.5 hour day, but looking at this, I think it may be wrong in not handling the break. What exactly are your criteria? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jeremy" wrote in message ... Wow. That certainly did something. I am a true novice with excel. What I would like to know is how did this calculate the difference between times if the only cell I referenced was the date cell? I don;t really understand what the formula did, but I changed it to meet the cells of the sheet that I have and it certainly gave me numbers. "ryguy7272" wrote: I think this will help you: =(NETWORKDAYS(A1,B1)-2)*8/24+((WEEKDAY(A1,2)<6)*(MAX(0,TIME(17,0,0)-MOD(A1,1))))-((WEEKDAY(A1,2)<6)*(MAX(0,TIME(8,0,0)-MOD(A1,1))))+((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(8,0,0))))-((WEEKDAY(B1,2)<6)*(MAX(0,MOD(B1,1)-TIME(17,0,0)))) I saw it on a post recently. The idea is quite brilliant; I certainly didn't come up with the concept... Anyway, hope it helps you out, Ryan-- RyGuy "Jeremy" wrote: Please help me. I need to find the time elapsed for workdays only between a column of dates and times (available in one column or two) and a fixed date and time. I only need hours of a work day. I have gotten so far as to get the whole number of workdays elapsed, but I need more precise data than that. 03373 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work days difference between 2 dates.
Hello,
Then I would suggest to use NETWORKDAYS. Regards, Bernd |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work days difference between 2 dates.
I tried that and only got the whole number of days difference. I need more
deatiled data than that. The data that I have is the date and time the order was created and I need to calculate the amount of hours elapsed between that date\time and and a given date. "Bernd P" wrote: Hello, Then I would suggest to use NETWORKDAYS. Regards, Bernd |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work days difference between 2 dates.
Bob, that was YOUR brainchild! I remember now. In it's original incarnation
it was set up to deal with 8.5-hour-days. -- RyGuy "Jeremy" wrote: I tried that and only got the whole number of days difference. I need more deatiled data than that. The data that I have is the date and time the order was created and I need to calculate the amount of hours elapsed between that date\time and and a given date. "Bernd P" wrote: Hello, Then I would suggest to use NETWORKDAYS. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I calculate a difference in work days? | Excel Worksheet Functions | |||
Difference between 2 dates, incl weekends, with variable work days | Excel Discussion (Misc queries) | |||
Difference between 2 dates, incl weekends, with variable work days | Excel Worksheet Functions | |||
difference betwwen two dates in days | Excel Worksheet Functions | |||
difference between two dates in years, months and days. | Excel Worksheet Functions |