Formula to get calendar day 15th

[Sasi Kumar]

Hi,

Can anyone help me with a formula to get calendar day 15th of the month basis the below sample conditions.

Ex1: If given date is 01/22/2016 then result should be 01/15/2016.

Ex2: If given date is 01/09/2016 then result should be 12/15/2016.

Ex3: If given date is 01/15/2016 then result should be 01/15/2016.

 

[vletm]

@Sasi Kumar
A1 & 3: d = given date
=date(year(d),month(d),15)
A2 ... maybe needs more information of logic ...?

 

[oldchippy]

@Sasi Kumar

With the given date in A1, then try this
=IF(A1-DATE(YEAR(A1),MONTH(A1),0)>14,DATE(YEAR(A1),MONTH(A1),0)+15,DATE(YEAR(A1),MONTH(A1)-1,15))

Edit: Can't seem to get this in one line?

 

[John Jairo V]

Hi to all!

With your ex. this formulae could be (something weird your second example):

=DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<15),15)

Blessings!

 

[Khalid NGO]

Hi Sasi Kumar,

You 2nd example is confusing, if it is a typo, you can use this too:
=EOMONTH(A1,-1)+15

Regards,

 

[Sasi Kumar]

Thank you all.

 

[Sasi Kumar]

Hi,

I need one more help on calendar day 15th formula shared above, which is working fine but need help on the below two report run date and timings:

First Report Run (Date & Time[24 hrs format]):

If the given date with time is 02/15/2016 15:30:00 and the actual report run date and time is every months calendar day 15th with 23:00:00, hence the result for the above given date and time should be 01/15/2016 23:00:00

Second Report Run (Date & Time[24 hrs format]):

If the report run date and time is every months calendar day 15th with 6:55:00 then the result should be 02/15/2016 6:55:00

Please help me with the formula accordingly.

 

[oldchippy]

Hi @Sasi Kumar

I'm not quite understanding your question?
Am I right in thinking that if the time is after (say) midday then it should be DATE + 23:00:00, but if it's before it should be the DATE + actual time?

 

[Sasi Kumar]

Hi,

The output date and time will not depend on midday or 23:00:00 hrs. The report run [Date & Time] are constant here. This wouldn't change. Whatever date and time the end user provides in the input field the out date and time changes taking into consideration of report run date and time and date and time provided by end user.

The actual report run [Date and Time] is every month calendar day 15th at 23:00:00 hrs.

Ex1: If the given date and time is 02/15/2016 15:30:00, then the result should be 01/15/2016 23:00:00

Ex2: If the given date and time is 02/15/2016 23:15:00, then the result should be 02/15/2016 23:00:00

 

[Jake Collins]

Dear Sasi

Which of the above formula are you using?

=IF(A1-DATE(YEAR(A1),MONTH(A1),0)>14,DATE(YEAR(A1),MONTH(A1),0)+15,DATE(YEAR(A1),MONTH(A1)-1,15))

or

=DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<15),15)

Let me know so I know which to adapt for your new requirements.

 

[oldchippy]

Hi @Sasi Kumar

To either formula just add to the end
+TIME(23,0,0)
Then custom format the cell to dd/mm/yyyy hh:mm:ss

 

[Sasi Kumar]

Hi Jake,

I am using the second one.

=DATE(YEAR(A1),MONTH(A1)-(DAY(A1)<15),15)

 

[Jake Collins]

Dear Sasi

For the First report run:

=DATE(YEAR(A1),MONTH(A1)-(DAY(A1)+MOD(A1,1)<15+23/24),15)+23/24

For the second report run:

=DATE(YEAR(A1),MONTH(A1)-(DAY(A1)+MOD(A1,1)<15+6.92/24),15)+6.92/24

Jake

 

[Sasi Kumar]

The formula is working as required. Thank you so much Jake.