Date format problem

[Richard]

Trying to get Sheet2 Column C to show a General format or
Number format of days subtracted from Sheet1 F$1 from
Sheet1 Column B. It shows a number like 38265 instead of 1
or 2 days. Here is my code.

My problem is here on Sheet2 Column C. Where I need the
Column to Subtract Sheet1 (F$1) from Sheet1 Column B. My
formula is =Sheet1!B3-F$1

Sheet1 (F1) = Now()

This is where I get Sheet1 Column B's date
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then 'CStr converts a numeric
value to a string
Application.Calculate 'Only needed if Calculation is set
to Manual
Sheet2.UsedRange.SpecialCells(xlCellTypeLastCell)(3,
1).EntireRow.Resize(3, 1) = Target.EntireRow.Resize(3,
1).Value
End If
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Column >= 1 And Target.Column <= 2 Then
If Application.CountA(Cells(Target.Row, 1).Resize(, 1))
= 1 _
And IsEmpty(Cells(Target.Row, 2)) Then
Cells(Target.Row, 2).Value = Now()
End If
End If
On Error GoTo inputagain
[a:a].SpecialCells(xlBlanks).EntireRow.Delete
inputagain: Exit Sub
End Sub

I have already tried all the format options to get a
number like 1 or 2 days to show instead of 38265.
Thanks in advance!!!

 

[macropod]

Hi Richard,

You seem to be going to a lot of effort for:

Result = Sheets(1).Range("B3").Value - Sheets(1).Range("F1").Value
If Result > 0 Then
'Do whatever'
Else
'Do something different (eg Sheets(2).Range("C1").Value = Result
End If

As for the 38265, that's just the datevalue for 5 October 2004.

Cheers
PS: You might also get better results using =TODAY() in F1, because that
returns an integer, whereas NOW() returns a decimal value that includes a
time of day component.

Trying to get Sheet2 Column C to show a General format or
Number format of days subtracted from Sheet1 F$1 from
Sheet1 Column B. It shows a number like 38265 instead of 1
or 2 days. Here is my code.

My problem is here on Sheet2 Column C. Where I need the
Column to Subtract Sheet1 (F$1) from Sheet1 Column B. My
formula is =Sheet1!B3-F$1

Sheet1 (F1) = Now()

This is where I get Sheet1 Column B's date
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then 'CStr converts a numeric
value to a string
Application.Calculate 'Only needed if Calculation is set
to Manual
Sheet2.UsedRange.SpecialCells(xlCellTypeLastCell)(3,
1).EntireRow.Resize(3, 1) = Target.EntireRow.Resize(3,
1).Value
End If
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Column >= 1 And Target.Column <= 2 Then
If Application.CountA(Cells(Target.Row, 1).Resize(, 1))
= 1 _
And IsEmpty(Cells(Target.Row, 2)) Then
Cells(Target.Row, 2).Value = Now()
End If
End If
On Error GoTo inputagain
[a:a].SpecialCells(xlBlanks).EntireRow.Delete
inputagain: Exit Sub
End Sub

I have already tried all the format options to get a
number like 1 or 2 days to show instead of 38265.
Thanks in advance!!!