Hi All,
Can any one provide solution for the follwing problem in excel.
I have to compare time between two dates, we have to exclude saturdays & sundays
and exclude non business hours. My business hours is 12:30 P.M to 23:30.
Reference :
From Date :1/31/11 3:17:01 PM (M/dd/yy)
To Date : 2/7/11 3:57:31 PM (M/dd/yy)
I need output in Hours, can any one help in this.
Have a look here: http://www.cpearson.com/excel/DateTimeWS.htm
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Perhaps:
=(A2-A1)*24
formatted as General gives total hours...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
NBVC, that will just give the hours difference whereas as the OP wants to exlude Sat & Sun and just include the business hours stated.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
I am still waking up.. I've only got one eye open...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi,
I tried this logic but it is not wroking.
My crietia is if the starting time is less than 13:30, it has to count as 13:30
if the Ending time is greater that 23:30 then it has to take end time as 13:30.
can u help in this issue.
It is good to provide macro or any good formula.
Thanks in advance,
Regards
Komal Manohar
Which did you try? Please provide the formula or even better a sample workbook.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks