hours between dates??

Hi all,

I have 2 questions really but hey are related to the same problem.

The problem is that i need to calculate the number of hours between 2 dates/times. I have been given the worksheet which has the 4 columns.
col1 = start date, col2 = start time, col3 = end date, col 4 = end time.

1) So, the first problem is that i need to concatente the startdate/time and end date/time. When i try this however it converts them into the machine date/time. e.g. =a1 & " " & b1 (a1 = start date, b1 = start time) ...becomes 39448 0.375. Can i avoid this and show them as a date and time.

2) And secondly i need to then work hours the total number of hours between the two dates excluding weekends??

Can anybody help on either problem? Many thanks in advance.

1) So, the first problem is that i need to concatente the startdate/time and end date/time. When i try this however it converts them into the machine date/time. e.g. =a1 & " " & b1 (a1 = start date, b1 = start time) ...becomes 39448 0.375. Can i avoid this and show them as a date and time.

Right-click and format as date and time in the format you want

To combine a date and time you don't concatenate, you add, i.e.

=A1+B1

format result cell to show date and time

....however, that step is unnecessary if you want to calculate work hours between the start and end times/dates.

Assuming that your dates won't be at weekends, and you want to count all weekday time (24 hours) you can use this formula

=NETWORKDAYS(A1,C1)-1+D1-B1

format result cell as [h]:mm

Where A1 is start date, B1 start time, C1 end date and D1 end time

Note: NETWORKDAYS is part of Analysis ToolPak add-in [unless you have Excel 2007, in which case it's an inbuilt function], if not installed use Tools > Add-ins and tick "Analysis ToolPak".

As for your first question, let's suppose the date is in column A and the time is in column B, the range starts from row 1:

=+CONCATENATE(DAY(A1),"-",MONTH(A1),"-",YEAR(A1)," ",HOUR(B1),":",MINUTE(B1))

Use =Weekdays() for your second question.

thanks for all your replies......

Right-click and format as date and time in the format you want

This doesn't work it just stays as the machine date and time 3951....

=NETWORKDAYS(A1,C1)-1+D1-B1

this one seems to only calculate the difference between the hours without taking in to account the days. e.g. 01/01/08 09:00 - 02/01/08 - 11:00 only returns 2 when it should return 26.

=+CONCATENATE(DAY(A1),"-",MONTH(A1),"-",YEAR(A1)," ",HOUR(B1),":",MINUTE(B1))

This works fine but the weekdays() function doesn't seem to appear when i try and use it. It's not in my function library, do i need to add it??


Many Thanks.

brilliant, thanks.