Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!)
    Thanks,

    pmatz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Thanks,

    pmatz

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!)
    Thanks,

    pmatz

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date/Time Calc (2003)

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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date/Time Calc (2003)

    How about

    =IF(E2<0,"-","")&INT(ABS(E2)/60)&":"&TEXT(ROUND(MOD(ABS(E2),60),1),"00")

    in F2 (I don't think SIGN would help here)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •