DevX Home Today's Headlines   Articles Archive   Tip Bank   Forums

# Thread: Calculate time in excel

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.

Smiley

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.
>
>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
•

 FAQ Latest Articles Java .NET XML Database Enterprise