Date With If Function

[Legacy 116183]

I am trying to use an If function with dates but can't seem to crack it.

What I want is: If today's date is later than 28/3/2022, then divide by 31. If it is before 31/3/2022, then divide by the number of days in the month which have elapsed.

I'm trying something like

=(B17/IF(TODAY()<DATE(2022,3,31),31,(2022,3,31)-today())) but not having any success

Any thoughts please?

 

[Haree]

Hi, In my opinion I think later than 28/2/2022 and before 31/3/22 is nearly the same. there is just a difference of two days. Or if it is specific you should use IF and AND function

 

[Legacy 116183]

You appear to have misread it I'm afraid.

There are 31 days between the 28th Feb and 31st March.

I'm am trying to divide something by the number have days which have elapsed in the month, but of the entire month has passed, I want it divided by the number of days in the month

 

[Haree]

Sorry it was a typo . But your question mentioned 28/3/22 and 31/3/22
From what I understand
You have a value of say 10,000 in B17
If the month is finished you want to divide it by the number of days in the month
If it has not finished you want to divide by number of days which have passed.
Am I Right ?
Kindly correct me if I am wrong

 

[Legacy 116183]

Sorry it was a typo . But your question mentioned 28/3/22 and 31/3/22
From what I understand
You have a value of say 10,000 in B17
If the month is finished you want to divide it by the number of days in the month
If it has not finished you want to divide by number of days which have passed.
Am I Right ?
Kindly correct me if I am wrong

Yes, that is right.

 

[Haree]

Kindly Try This

=B17/IF(TODAY()>=DATE(2022,3,31),31,DAY(TODAY()))

 

[Legacy 116183]

Kindly Try This

=B17/IF(TODAY()>=DATE(2022,3,31),31,DAY(TODAY()))

That works fine.

Thanks very much.

 

[Haree]

Glad we were of help. Thank you for your feedback