View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vezerid
 
Posts: n/a
Default FORMULA NEEDED TO COUNT DAYS IN EXCEL BETWEEN DATES

Given two dates, you can find the number of days between them by simple
subtraction. However, it is best to enclose the difference in the
VALUE() functino, because otherwise Excel tends to format the result as
date:

=VALUE(B2-A2)

On your second question: You do not specify whether you record time. It
seems you only record dates. Then, the formula needed is probably:

=VALUE(B2-A2)*8

assuming that a working day has 8 hours.

For quick date/time entry you can use the combinatinos Ctrl+; (which
will enter the current date) and Shift+Ctrl+; (which will enter the
current time).

For further automation, let us assume that your data are to be entered
in column C:C and the data entry time in Column D:D. Then the following
event macro will automate this process:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C:C")) Is Nothing Then
Cells(Target.Cells(1,1).Row, Target.Cells(1,1).Column+1) = Format(Now,
"dd-mm-yyyy hh:mm")
End If
End Sub

Notice:
Change the construct "C:C" to whatever column is to hold data entry.
The row that starts with "Cells(" is to be a single line with hh:mm")
To install:
Right-click the sheet tab, select View Code, which will bring you to
VBA.
Paste the above code after possibly changing something.

HTH
Kostis Vezerides