excel...

This is a discussion on excel... within the A Brief History of Cprogramming.com forums, part of the Community Boards category; I was at work and my boss asked if I knew anything about excel. I said no. He was wondering ...

  1. #1
    Registered User linuxdude's Avatar
    Join Date
    Mar 2003
    Location
    Louisiana
    Posts
    926

    excel...

    I was at work and my boss asked if I knew anything about excel. I said no. He was wondering if I knew how to have a formula that takes two times and figures out how many hours that person worked, I looked at the syntax and gave him the answer, but he wanted the time rounded to quarter of hours. How would I go about doing that. Here is what I have(I know really simple)
    Code:
    =c1-b1

  2. #2
    & the hat of GPL slaying Thantos's Avatar
    Join Date
    Sep 2001
    Posts
    5,681
    well you can use the minute() function to extract the number of minutes in your calculation. Then you just to need to find a way to round it.

  3. #3
    Its not rocket science vasanth's Avatar
    Join Date
    Jan 2002
    Posts
    1,683
    ok considering B1 is the from time and C1 is the to time... and d1 should display the number of hours then the following is the formulae in d1

    Code:
    =HOUR(C1-B1)/24+(IF(MINUTE(C1-B1)<15,0,IF(MINUTE(C1-B1)<30,15,IF(MINUTE(C1-B1)<45,30,45)))/(24*60))
    but make sure b1,c1 and d1 cell format is that of time

    now the formulae will roud the time the nearest lower quater... this can be changed as required..

  4. #4
    &TH of undefined behavior Fordy's Avatar
    Join Date
    Aug 2001
    Posts
    5,786
    An easier way is to record the date in hh:mm format (IE 9AM as 09:00 and 5PM as 17:00). then you can add and delete and it will show the hours.

    If you want to SUM the hours it gets a little weird...but if you change the cell format of the total cell to "[h]:mm" it works out AOK

    Saves all those unsightly clunky formulas

    See attached for a demo

    [edit] Oh and this is better suited for the Tech board...Not GD
    Attached Files Attached Files

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Data Organization and Excel
    By uniqst3r in forum C Programming
    Replies: 4
    Last Post: 05-26-2008, 01:37 AM
  2. using Excel to connect to external data source
    By George2 in forum Windows Programming
    Replies: 4
    Last Post: 05-01-2007, 10:07 PM
  3. Exporting a DataTable to Excel (.NET)
    By Trauts in forum C# Programming
    Replies: 0
    Last Post: 07-29-2005, 01:03 PM
  4. C++ Builder and Excel files
    By mfdemi in forum Windows Programming
    Replies: 3
    Last Post: 09-24-2004, 01:51 AM
  5. Print a File via Excel
    By ginoitalo in forum C# Programming
    Replies: 2
    Last Post: 05-10-2002, 12:33 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21