DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Calculate time in excel

  1. #1
    Smiley Guest

    Calculate time in excel


    Hi group -

    I'm new to vba so please excuse me if this is a dumb question. I'm trying
    to create a small scheduling app in excel and I need to create a formula
    or code that will automatically calculate the hours worked for the total
    week instead of typing in it manually. For example:

    Mon Tues Wed Thur Fri Sat Sun Total hrs
    8a - 4p off off 5p - 9p off off 4p - 9p 17

    I'm at lost of how I begin this. A co-worker referred me to this site for
    assistance as he could not help. All responses would be greatly appreciated.

    Thanks in advance,
    Smiley

  2. #2
    megafirexxx Guest

    Re: Calculate time in excel


    Hi Smiley,

    You might wan to separate the start time and end time for each day into 2
    columns. Something like this:

    MONDAY | |TUESDAY| |TOTAL
    -------------------------------
    Start |End |Start |End |
    -------------------------------
    8am |5pm |8am |4pm |

    It would be advisable to set the format of the cells for inputing the time
    to "Time" format.

    Then you can use this function in your macro to get the total time for one
    day, which you can loop it to get for the whole week.

    Function cbfCalculateTime()
    Dim ld_from As Date
    Dim ld_to As Date
    Dim li_period As Integer
    'Assume Cell A3 contains Start Time
    ld_from = Sheet1.Cells(3, 1)
    'Assume Cell B3 contains End Time
    ld_to = Sheet1.Cells(3, 2)
    'Calculate the duration by using DATEDIFF function
    cbfCalculateTime = DateDiff("h", ld_from, ld_to)
    End Function

    Hope this helps...

    Regards,
    MegafireXXX


    "Smiley" <smileychgo@msn.com> wrote:
    >
    >Hi group -
    >
    >I'm new to vba so please excuse me if this is a dumb question. I'm trying
    >to create a small scheduling app in excel and I need to create a formula
    >or code that will automatically calculate the hours worked for the total
    >week instead of typing in it manually. For example:
    >
    >Mon Tues Wed Thur Fri Sat Sun Total hrs
    >8a - 4p off off 5p - 9p off off 4p - 9p 17
    >
    >I'm at lost of how I begin this. A co-worker referred me to this site for
    >assistance as he could not help. All responses would be greatly appreciated.
    >
    >Thanks in advance,
    >Smiley



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center