# Thread: Work out date from hours (Excel 2000 >)

1. ## Work out date from hours (Excel 2000 >)

Hi,

I'm thinking in circles...help!

Say I have a start date in a column, and then a couple of columns with work hours...adding up to say, 64 hours. I need a formula that will display an "End date" that corrosponds to the elapsed hours.
EG: 24/8/2005, 24, 24, will equal 26/8/2005. (simplified)

Any ideas of a function/formula to accurately display the appropriate date!
Tx

2. ## Re: Work out date from hours (Excel 2000 >)

Dates are stored with 1 day as unit, so you must convert everything to days.
Say the start date is in A2, and B2 through D2 contain hours. A formula for the end date could be

=A2+SUM(B22)/24

The result will still contain a time component, you can round to the nearest midnight by using

=ROUND(A2+SUM(B22)/24,0)

or round up to the next midnight by

=CEILING(A2+SUM(B22)/24,1)

3. ## Re: Work out date from hours (Excel 2000 >)

Fantastic Hans...this should do just grand!!
Cheers

4. ## Re: Work out date from hours (Excel 2000 >)

Thanks for the formula Hans. I got some feedback from the client who says: The problem that remains is this: What happens if a project begins on a Friday and continues for a few days. How will the formula be able to deal with weekends. If I start a 3-day project on Friday, the formula should return date of completion as the following Tuesday and not Sunday.

Tx again for the help Hans. Hope this makes sense!
Cheers

5. ## Re: Work out date from hours (Excel 2000 >)

If you activate the Analysis ToolPak add-in that comes with Excel (in Tools | Add-Ins...), you can use two functions that deal with work day / weekend day arithmetic. The description of WORKDAY in the online help says
<hr>Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.<hr>
and that of NETWORKDAYS
<hr>Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.<hr>
Look up these functions in the Excel help for details and examples.

6. ## Re: Work out date from hours (Excel 2000 >)

Hans, tx for the feedback.
Cheers

7. ## Re: Work out date from hours (Excel 2000 >)

I don't understand the calculation. Can you explain in detail how it fits together? For example, are Hours1, Hours2 and Hours3 contiguous?

8. ## Re: Work out date from hours (Excel 2000 >)

The project starts on 26/08 at 12H00pm. The Hours1 represents 8 hours worked on the project after 12H00pm...say from 1pm to 9pm on 26/08. Then hours2 is a further 13 hours say on Saterday 27/08 and hours3 is 7 hours on Sunday 28/08. As all these hours are over a weekend and they total to 28 the project end date will be Monday and not Sunday. 28 hours is represented by 2 days, but weekends must not be taken into account...so the end date is Monday!
Hope its a little clearer and I'm making sense?

9. ## Re: Work out date from hours (Excel 2000 >)

Lik Hans, I don't understand your calculation, but does this formula do what you want:

<code>
=WORKDAY(A3,B3/24+C3/24+D3/24)
</code>

10. ## Re: Work out date from hours (Excel 2000 >)

Does 8 hours represent one work day, or one third of a work day? What does 13 hours represent? I think that you have some real problems with this approach.

11. ## Re: Work out date from hours (Excel 2000 >)

Yes Legare,
This is going what I need. Tx a million!!! All those calcs below the hours you must ignore. That was my futile approach to trying to solve the answer. Based on your approach:
Say I have: 26/08 the 24hrs then 24hrs and another 24hrs. Your calc works out 31/08 which is correct. In other words starting on Fri 26/08 and adding 72 hours (3 days) it returns Wed 31/08 and not Mon 29/08 as it must ignore weekend days!