1. ## Date/Time Calc (2003)

Hi
I have got a calculation in a spreadsheet that gives me the amount of minutes difference between two times, with either a positive or negative number in minutes. I have used Data validation to ensure the user enters a time in the format hh:mm in the two cells A2 and B2. C2 does the calculation and D2 gives the result.

Can this be 'enhanced' or is there a way to incorporate this calculation to include the Date as well as the time.

I cant see how to get the Data Validation to only accept formats like dd/mm/yy,hh:mm, and am not sure where to start in formatting the cells to store the data like this.

I know that Now() gives the Date and Time, but have got a bit stuck in my thinkging of how to do this.

Would it be better to have 2 seperate cells, one for date and one for time? I guess then I could validate both and perform the calculation easier, but I would ideally like to keep the data to 1 cell. Fussy I know...

TIA

2. ## Re: Date/Time Calc (2003)

You'd need custom validation to check date+time values, and it would involve either a complicated formula or a user-defined VBA function (I've attempted neither). Separate cells for date and time are considerably easier - see attached version. Note that the calculation of the number of minutes is very easy: just multiply the difference with the number of minutes in a day, 1440.

3. ## Re: Date/Time Calc (2003)

Thanks Hans, I gotta agree it is nice and simple this way - thats fine and allows for simple Data Validation on each cell - cool.

I need the minutes to only count from 8am till 4.15pm if there is a difference in days, to cater for working hours, and this would normally just be for a day difference of 1.

So if Planned was 01/05/05 15:15 and Actual was 02/05/05 9:30 then the difference should be 150minutes (60+90). How would you do that ? (Hope its ok to ask!)

4. ## Re: Date/Time Calc (2003)

Try this formula in E2 in the workbook I attached earlier:

=C2+D2-A2-B2+((C2<A2)-(C2>A2))*15.75/24

(15.75 is the numer of hours between 16:15 on one day and 8:00 the next day)

This assumes that
- The date in C2 will not be more than one day before or after the date in A2.
- The times in B2 and D2 are between 8:00 and 16:15 (you could enforce this in the validation for these cells).

5. ## Re: Date/Time Calc (2003)

Or even simpler:

=(C2-A2)*8.25/24+D2-B2

This will allow the dates to be more than 1 day apart.

6. ## Re: Date/Time Calc (2003)

Thanks Hans, have put that in and am looking into it to see how it works etc..

I think I am going to have to use some =IFs after all though, because I have to cater for weekends as well [img]/forums/images/smilies/sad.gif[/img]

also, if the date and time were the same in both cells, the calculation gives -3.49241E-09...

will try and get this sorted today and post back with results. Thanks again for the tip off

7. ## Re: Date/Time Calc (2003)

The -3.49241E-09 is a rounding error. Set the number format to display 2 decimals only or so.

If you activate the Analysis ToolPak add-in (in Tools | Add-Ins...), you can use the NETWORKDAYS function to exclude weekends, and even public holidays if you create a table of those. You'd still need lots of IFs.

8. ## Re: Date/Time Calc (2003)

Thanks Hans.

I realised that must have been an error when i got some rounding errors using MOD() !! The analysis toolpak is installed already - I forgot about networkdays - oops <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

Well, I am nearly there I think. The calculation takes into account for Weekdays only, and potential holidays thanks to NETWORKDAYS.

Haven't cracked getting the F column to reflect + or - time, as needed to use ABS() to stop some erroroneas results! i'm sure there must be a SIGN() type of function somewhere! (or an IF if i have to!)

9. ## Re: Date/Time Calc (2003)

Yes, there is a SIGN function. I'll look at your attachment later on (if nobody else does)