Récupérer date dans format datetime VBA

Option Explicit 
' 

Sub Test() 

    Dim JourIntervalle As Date 
    Dim HeureDebut As Date 
    Dim HeureFin As Date 
    Dim SommeEvenement As Variant 
    Dim NbEvenement As Long 
    
     
    Dim j As Integer 

    JourIntervalle = Now 
    HeureDebut = Now 
    HeureFin = Now 

    j = 1 
   
    While Format((Sheets("feuil1").Range("A" & j).Value), "YYYY-MM-DD") = Format(JourIntervalle, "YYYY-MM-DD") 
        If (Format((Sheets("feuil1").Range("A" & j).Value), "HH:MM:SS") >= Format(HeureDebut, "HH:MM:SS")) And _ 
            (Format((Sheets("feuille1").Range("A" & j).Value), "HH:MM:SS") <= Format(HeureFin, "HH:MM:SS")) Then 
            SommeEvenement = SommeEvenement + Sheets("feuil1").Range("D" & j).Value 
            NbEvenement = NbEvenement + 1 
        End If 
        j = j + 1 
    Wend 

End Sub 
' 

Sub Test()

    Dim NbJours As Long
    
    Dim Valeur1 As Variant
    Dim Valeur2 As Variant
    
    Valeur1 = Sheets("feuille1").Range("A2").Value
    Valeur2 = Sheets("feuille1").Range("A2").End(xlDown).Value
    
    NbJours = DateDiff("d", Valeur1, Valeur2) + 1

    MsgBox NbJours

End Sub
'

Dim Jour As Long 
Dim Jour As Long 
Dim Heure As Double 
With Sheets(1) 
     'mémorisation
     Jour = Int(Range("A1")) 
     Heure = Range("A1") - Jour 
     'restitution 
     With Range("A2") 
          .Value = (Jour+10) + (Heure+120/1440) 
          .NumberFormat = "dd/mm/yyyy h:mm;@" 
     End With 
End With