Date and IIF

[scubadiver]

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

 

[Dave Peterson]

Maybe...

Dim A as date
Dim B as date

'how does A get populated???
'test data
a = dateserial(2008,1,2) + timeserial(12,30,45)

if day(a) < 11 then
b = dateserial(year(a), month(a), 1)
else
b = dateserial(year(a), month(a) + 1, 1)
end if

or as one line:
B = DateSerial(Year(A), Month(A) - CBool(Day(A) > 10), 1)

What's N?

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

 

[Kevin B]

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis

 

[scubadiver]

I *was* looking for the Access forum, d'oh!

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis
--
Kevin Backmann

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

 

[Chris Bode via OfficeKB.com]

Please follow following steps

At first format the cell in column B to display date and time formate as:-
Select a cell in B(say B1)>right click>format cells>select number tab>select
date from category list>select 3/14/01 13:30 from type list.
Similarly convert cells in column A in date time format too.
Then,
1.Select a cell in column B (say B1)
2.Enter following formula
=IF(DAY(A1)<11,DATE(YEAR(A1),MONTH(A1),1),IF(DAY(A1)>=11,DATE(YEAR(A1),MONTH
(A1)+1,1)))
3.Similarly apply for other required cells in column B

Have a nice time…


Chris

 

[scubadiver]

Thanks for that Kevin. It looks like it has worked.

Cheers!

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis
--
Kevin Backmann

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!