# Calculate time in excel

• 08-07-2002, 05:36 PM
Smiley
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.

Smiley
• 08-08-2002, 02:09 AM
megafirexxx
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.
>