Time issue

[MJKelly]

Hi,

I am using code to fill data onto a ws and once the values have been
pasted, the format is changed to "hh:mm", however, time values which
occur before 06:00 also have the date (01/01/1900) added to them and
this means a calculation which determins the time between the start
and finish times does not work. Any ideas why excel is adding this
date data? If I overwright the time value the caluication works. The
code I am using to set the format is: -

With Selection
.NumberFormat = "hh:mm"
End With

can you help?
Regards,
Matt

 

[Mike H]

Can we see the code used to fill the data range?

Mike

 

[Joel]

You are saving just the time and not the date. Excel time is stored as a
Number with Jan 1, 1900 = 1 and everyday after equals 1. 39771 is Nov 19,2008.

A day starts at midnight and one hour is = 1/24. One minute is 1/(24 * 60).
You have stored just the time on your worksheet which excel defautls to Jan
1, 1900. there is nothing wrong with this as long as you format the
worksheet as just time (without a date) and you don't need to compare the
time with previous days.

If you enter 12:00 it will be entered as .5 (1/2 day), 24:00 as 1, 36:00 as
1.5.

If you need to use the date then you have to enter the date with the time.
You have the option of formating at Date/time (11/19/08 8:00 AM) in any
format you want. You can format to show the time without the date. Or you
can format to show the date without the time. Or you can have both the date
and time. When you enter a date excel defaults the time to midnight. when
you enter a time excel defaults the date to Jan 1, 1900.

 

[MJKelly]

Hi,

The bit which calculates the time between start and finish is:-


StartTime = r1.Value
EndTime = r1.Offset(0, 1)
TotalHours = EndTime - StartTime


After creating the new workbook using a template target sheet whcih is
copied numerous times, the code is (after this code I just format the
time cells as in my previous post): -

Dim r1 As Range
Dim Sh As Worksheet
Dim sh1 As Worksheet
Dim Person As String
Dim PayNo As String
Dim StartTime As Date
Dim EndTime As Date
Dim TotalTime As Date
Dim Task As String
Dim TargetShift As String
Dim Target552 As String
Dim Drop As Range

ThisWorkbook.Activate

For Each r1 In ThisWorkbook.Sheets("Overtime").Range("D320")
If Not r1.Value = "" Then


If r1.Value >= 0.25 Then TargetShift = "Early"
If r1.Value >= 0.58264 Then TargetShift = "Late"
If r1.Value >= 0.91597 Then TargetShift = "Night"
If r1.Value < 0.25 Then TargetShift = "Night"

Select Case r1.Offset(0, 4).Value
Case Is = "Proc M"
Task = "Processing"
Case Is = "XD"
Task = "Cross Docking"
End Select

Person = r1.Offset(0, -2)
PayNo = r1.Offset(0, -3)
StartTime = r1.Value
EndTime = r1.Offset(0, 1)
TotalHours = EndTime - StartTime

If TargetShift = "Early" And Task = "Processing" Then
Target552 = "Early Processing"
If TargetShift = "Late" And Task = "Processing" Then
Target552 = "Late Processing"
If TargetShift = "Night" And Task = "Processing" Then
Target552 = "Night Processing"
If TargetShift = "Early" And Task = "Cross Docking"
Then Target552 = "Early Cross Docking"
If TargetShift = "Late" And Task = "Cross Docking"
Then Target552 = "Late Cross Docking"
If TargetShift = "Night" And Task = "Cross Docking"
Then Target552 = "Night Cross Docking"

For Each sh1 In Workbooks(NewBook).Sheets
sh1.Activate
If sh1.Name = Target552 Then

ActiveSheet.Range("A10000").End(xlUp).Offset(1,
0).Select
With Selection

.Offset(0, 0).Value = Person
.Offset(0, 1).Value = PayNo
.Offset(0, 3).Value = StartTime
.Offset(0, 4).Value = EndTime
.Offset(0, 5).Value = TotalHours

End With

End If
Next sh1
End If

Next r1

 

[Joel]

Just reformat the cell

from
Offset(0, 5).Value = TotalHours

to
Offset(0, 5).Value = TotalHours
Offset(0, 5).numberformat = "HH:MM"

 

[MJKelly]

Just reformat the cell

from
Offset(0, 5).Value = TotalHours

to
Offset(0, 5).Value = TotalHours
Offset(0, 5).numberformat = "HH:MM"



















- Show quoted text -

Thanks Joel,

It works now, however I have also forced the format for the start and
end times as you have advised above (and removed the code to format
the range afterwards). But it did not change the error. I then
changed the original time entries. They used to be drop down
validation (to force ten minute intervals), and I have now removed the
validation and entered them manually, and it seems to work. Now I
just have the problem of forcing ten minute entries without cell
dropdown validation, but I have started working on this and think I'm
nearly there.

Thanks loads for your help.
kindest regards,
Matt

 

[Joel]

The times may be strings. check the format of the drop down box and see what
the format is. also make sure there isn't a single quote in front of the
times. You can convert the string time to a real time format using the
function DateValue().