PDA

View Full Version : Calculating Endtime/Duration



lukayeh
01-07-2013, 02:59 AM
Hi I'm creating a time-sheet in Excel and want to be able to calculate the duration based on start and end time, however I also want it to be able to calculate the end time if end time cell is left blank and duration is filled in? I'm quite new to VBA so i hope you can help! Thanks

Teeroy
01-07-2013, 03:12 AM
Hi lukayeh, can you post a sample workbook to show how your data will be placed on the worksheet? Otherwise you can get code designed for columns when you're working in rows etc.

lukayeh
01-07-2013, 03:27 AM
Well i haven't quite figured out the layout yet but lets say:
A2 the start time formatted hh:mm AM/PM
B2 the end time formatted hh:mm AM/PM
C2 the duration hh:mm "hrs"
What i want for is if B2 is left empty and C2 is filled in B2 is calculated but if B2 is filled in, C2 will be calculated, just not sure how to go about doing it as at somepoint all three could be filled in causing confusion :dunno

lukayeh
01-07-2013, 04:09 AM
I've attached my spreadsheet, what i've managed to do so far, i've added spinners to the start and end times but these might over complicate things as they require formulas in the start and end cells

Teeroy
01-07-2013, 04:35 AM
The attached spreadsheet has some code that might help you. It's pretty raw though, you'd probably need error handling.

lukayeh
01-07-2013, 06:23 AM
That works great, I'm looking to use a dialog box to change the times, however when submitted this doesn't update the values, for example if i use the dialog to change start and finish times it doesn't update the duration, I understand that the macro runs when the worksheet is changed but how do i do it so it updates with dialog input?
Thanks for your help so far!

Sub Transfer_Time()

Worksheets("Sheet1").Range("a2").Value = DialogSheets("Dialog1").EditBoxes("Edit Box 4").Text
'Worksheets("Sheet1").Range("b2").Value = DialogSheets("Dialog1").EditBoxes("Edit Box 6").Text
Worksheets("Sheet1").Range("c2").Value = DialogSheets("Dialog1").EditBoxes("Edit Box 8").Text

End Sub

As i said i'm new to VBA so that code is very basic for the dialog box :dunno

Teeroy
01-07-2013, 12:25 PM
I haven't used DialogSheets myself but I'd recommend using a UserForm over a DialogSheet. DialogSheets were replaced by UserForms quite a while ago (excel 95?) and have really been maintained for backward compatibility. In excel 2007 onwards they're hidden objects to the object browser so you won't get any IntelliSense help when trying to write code for them.