hello again
i have two dates in this format:
start: 17.07.2009 11:00:00
end: 17.07.2009 14:00:00
i need to check if the period in between those is during a workday.
our workday starts at 10:00:00 and finishes at 19:00:00
so my expected result is 3 hours. (1400-1100)
other example:
start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00
result is 1900-1100 + 1100-1000
= 9 hours
because it is during two workdays...
is there a way to calculate this in VBA?
thanks
Last edited by f_mayr; 07-23-2009 at 03:36 AM.
it would be a possibility calculating the hours which are not during a workday. actually that would be better...
So are weekends treated as a "workday" - your 2nd example would imply this to be the case ?
It might make more sense to provide a sample file as it's all a little ambiguous at present, ie where are the values (cells, variables) etc etc...
in native formula terms where A1 is start & A2 end and where weekends excluded:
EDIT: to get the remainder (ie out of hours) you can of course use: =(A2-A1)-formula
Please Login or Register to view this content.
Last edited by DonkeyOte; 07-22-2009 at 05:30 AM.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
well yeah initially i tried it with the networkdays. but it never worked.
if i use your formula like this:
A1: 17.07.2009 10:00:00
A2: 22.07.2009 11:00:00
my expected result would be
3 days and 1 hour.
means:
the 17th from 1000->1900
the 18th 0 (weekend)
the 19th 0 (weekend)
the 20st 1000->1900
the 21st 1000->1900
the 22nd 1000->1100
which would be
9 hours +
9 hours +
9 hours +
1 hour
but the result the formula gives back is 01 day and 4 hours
formated like this: TT "Days" hh:mm:ss "Hours"
and i dont understand why..
thats why i tried another approach. counting the whole difference between two dates minus the weekends and minus the night hours...
Last edited by f_mayr; 07-22-2009 at 05:49 AM.
In hourly terms the result of your two dates is as you say 28 hours (9+9+9+1) which as far as XL is concerned represents 1 day and 4 hours.
To get XL to interpret the 28 hours as 3 days and 1 hour you would need to adjust the formula to account for the fact that 1 day = 9 hour - in truth you may find it easier to do that in a subsequent calculation as opposed to altering the already lengthy formula. To reiterate the formula is working per se - it's what you do with the resulting value that needs to be tweaked.
aha so trivial of course
allright ill check that. thanks
is it possible to format that result in hours only? (without days)
If by that you mean display 28:00 then yes, use a Custom Format of: [hh]:mm
yes thats what i meant. now what i still need to have would be holidays. if i remember correctly you can add that to the networkdays formula.
how can i add it into your formula above when i have a range (lets say from C1:C20) with holidays in it.
do i have to add it on each networkdays in the formula (hence 3 times)?
Yes, your holiday range is the optional third parameter of the NETWORKDAYS function so repeat range in each instance of function.
allright. so that would basically look like this:
correct?Please Login or Register to view this content.
Yes though the fact you ask makes me think it's not working... ?
no it seems to work perfectly. i made a mistake in counting 4 months manually
but for the holidays i just wanted to make sure that works. im not to good in xl
i will check this now..
thanks again
yes that seems also to work.. thats great
im sorry i have to bother you that much but i really dont get on on myself...
now i have following set up:
(total time is the result of the above networkdaysformula)
new, open, in progress, closed are states of the issues..
an issue always starts with new but can change randomly after that in any state (even in new again)
ex. table:
issue1, new, open, totaltime1
issue1, open, new, totaltime2
issue1, new, in progress, totaltime3
issue1, in progress, close, totaltime4
issue2, new, in progress, totaltime5
issue2, in progress, close, totaltime6
issue3....
and so on
now what i need to do is group issues after their number and count the total time each state is "using"
in case issue1:
new = totaltime1+totaltime3
open = totaltime2
in progress = totaltime4
and so on.
is that possible somehow?
i thougth about doing it somehow like this...
:Please Login or Register to view this content.
My advice... use a Pivot Table... ideal for this type of analysis.
To setup - set "Issue" column to be the Row Field in the PT, "State1" column to be the Column Field and "Total Time" column as the Data Field (set to Sum: apply a Custom Number Format of [hh]:mm)
Without having a sample to work with these type of questions become very difficult to answer properly I'm afraid as the layout is not clear etc etc...
yes thats helping alot. thank you very much for your help!
Glad to hear it.
I am moving this thread to Worksheet Functions given in the end it was not a VBA resolution.
just another question. im putting it in here that i dont have to start a new topic.
with the above formula i get days in 24 hour format. means 1 day=24 hours
my question is how is excels behaviour with months?
for example how many days are 2 months and how many hours?
basically i need to convert (manually just for checking if the figures are correct)
something like this "02 months 18 days 18:52:08 hours" into the total amount of days and hours.
thanks
Not sure I follow...the underlying value of any custom formatted value remains numeric, ie if you're wanting to push the value into a 9 hour day format you can just work with the underlying time value. An example will help here... I'm struggling to visualise what you have and what you want...
no its not a 9 hours problem
maybe this can help:
i have
A1: 20 Days 19:46:49 Hours
A2: 12 Days 01:30:19 Hours
B1: =A1+A2 = 01 Days 21:17:08 Hours
All 3 cells with this formating: TT "Days" hh:mm:ss "Hours"
A1 and A2 are results of calculating formulas.
now the first problem is obvious. the total (B1) should be 1 month 01 Days 21:17:08 Hours.
this is achieved by changing the formating to: MM "Months" TT "Days" hh:mm:ss "Hours
now the problem i have is that A1 and A2 both count 1 month if i format them to MM "Months" TT "Days" hh:mm:ss "Hours
That means with this formating i have:
A1: 01 Months 20 Days 19:46:49 Hours
A2: 01 Months 12 Days 01:30:19 Hours
B1: =A1+A2 = 02 Months 01 Days 21:17:08 Hours
that is not correct! why is excel giving them one month even if there is no month in the calculation result..?
The correct result would be:
A1: 00 Months 20 Days 19:46:49 Hours
A2: 00 Months 12 Days 01:30:19 Hours
B1: =A1+A2 = 01 Months 01 Days 21:17:08 Hours
hope you can help me..
If you set the format of A1 & A2 to [hh]:mm:ss what are the returned values ?
In a Custom Format XL will only display days up to 31 ... unlike time where you can use [ ] to show cumulative you can not do this with days, months etc... using Custom Formats to represent accurate aggregation of dates is at best awkward and open to error...
if i use the [] on all 3 cells it shows me:
A1: 499:46:49 Hours
A2: 291:02:26
B1: 790:49:15
which (as calculation) is correct. but im not sure if my results (A1 and A2) are still correct. what exactly does it do?
count the days in hours?
allright i checked some values and it seems to be correct.
The Custom Format you're applying relates to the datetime value that XL is generating... so the summation of time to: 790:49:15 is as far as XL is concerned is the equivalent value to the following date: 1st Feb 1900 22:49:15 ... thus when the time summation cell is formatted as m "Months" d "Days" h:mm:ss "Hours" you will get 2 Months 1 Days 22:49:15 Hours because you're formatting the Date value itself rather than the time value as a difference if that makes sense at all ?
Perhaps you want something like:
Please Login or Register to view this content.
Per my note at MrExcel.com
Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!
http://www.mrexcel.com/forum/showthread.php?t=405388
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks