Syntax of Formula with Date to be written in textBox

[NimishK]

Wish you all Happy New Year

Hello
1. What would be the syntax for the following to write in txtAmt.text
=IF(AP6<$D$2,AQ6,0)+IF(AS6<$D$2,AT6,0)+IF(AV6<$D$2,AW6,0)+IF(AY6<$D$2,AZ6,0)+IF(BB6<$D$2,BC6,0)
the above is written in BF6 Cell

so that anyone opening the sheet gets the cell value as above formula
AP6, $D$2 are values of Date specifically $D$2 is Todays Date in cell D2 =Today() displayed as 01-01-2019
AQ is the Numerical value and what could be meaning of 0 in =IF(AP6<$D$2,AQ6,0)

Cell AP6 is displayed as 17-Oct-2018 how can this be represented in as is txtInstDate.Text
Because ive coded as
Dim tDate As String, t2Date As String
tDate = Format(Date, "dd-mm-yyyy")
txtInstDate.Text = tDate which is displayed as 17-10-2018

t2Date = Format(Date, "dd-mm-yyyy")
txtInst2Date.Text = t2Date which is displayed as 11-01-2018
but for above t2date which actual is 01-11-2018 in cell value displayed as 01-Nov-2018 then t2Date is displayed as 11-01-2018 which is confused to understaning as 11th Jan 2018 instead of actual which is 1st November 2018. what would be ideal in regards to considering above formula

Thanks and Regards
NimishK

 

[Yongle]

You can use any of the event macros to do this

try this in sheet module

Private Sub Worksheet_Calculate()
    txtAmt..Value = Format(Range("BF6"), "dd-mm-yyyy")
End Sub

 

[KKaren]

If you want to display the month with the 3 characters ("Oct"), then you can simply add one more "m" in the format function:

tDate = Format(Date, "dd-mmm-yyyy")

 

[Yongle]

oops
- just spotted extra "." in post#2

should of course be

Private Sub Worksheet_Calculate()
    txtAmt[COLOR=#ff0000].[/COLOR]Value = Format(Range("BF6"), "dd-mm-yyyy")
End Sub

 

[NimishK]

1. What would be the syntax for the following to write in txtAmt.text
=IF(AP6<$D$2,AQ6,0)+IF(AS6<$D$2,AT6,0)+IF(AV6<$D$2,AW6,0)+IF(AY6<$D$2,AZ6,0)+IF(BB6<$D$2,BC6,0)
the above is written in BF6 Cell
so that anyone opening the sheet gets the cell value as above formula
AP6, $D$2 are values of Date specifically $D$2 is Todays Date in cell D2 =Today() displayed as 01-01-2019
AQ is the Numerical value and what could be meaning of 0 in =IF(AP6<$D$2,AQ6,0)

What about above syntax to be written as code and meaning of 0 in =IF(AP6<$D$2,AQ6,0)