I have call data in date/time 06/07/08 2:00 PM custom format for a 2 month period. I have my regular opening times eg Monday 93, Tuesday 104. For each call I want to know if it was made during opening times or not. Thanks.
Calculate Total Hours Difference For Date & Times



Re: Calculating Times Of Calls  In Hours Or Not
=A1B1 and custom format as [h]:mm works for TRUE dates & times

Re: Calculate Total Hours Difference For Date & Times
thanks, but not sure what you mean  what are A1 and B1 in this case?

Re: Calculate Total Hours Difference For Date & Times
Good grief and you consider yourself above average. LOL!

Re: Calculate Total Hours Difference For Date & Times
Happy to be stupid if that is a category.
Perhaps I should have said: What values am I assigning to the two cells A1 and B1 given that I have 3 inputs  a call time, daily opening time and daily closing time.
thanks


Re: Calculate Total Hours Difference For Date & Times
Think Dave was indicating that you can perform normal maths on true dates and times, therefore testing whether values are higher/lower than each other.

Re: Calculate Total Hours Difference For Date & Times
Hello David,
Lets assume you have a list of call date/times in A2 down
In E2:E8 list the days of the week, with corresponding start times in F2:F8 and closing times in G2:G8, so E2 would be Monday, F2 would be 09:00 and G2 15:00. If you don't open some days you can leave those out altogether or just leave the opening and closing times blank.
Now in B2 use this formula copied down
=IF(AND(VLOOKUP(TEXT(A2,"dddd"),E2:G8,[COLOR="Red"]2[/COLOR],0)<=MOD(A2,1),VLOOKUP(TEXT(A2,"dddd"),E2:G8,[COLOR="red"]3[/COLOR],0)>=MOD(A2,1)),"Within Work Hours","Outside Work Hours")

Re: Calculate Total Hours Difference For Date & Times
thanks for your help Daddylonglegs
Problem all solved
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!