Hi All,
I need to find the below and wondering how do i write a formula for this.
Consider today as 25-Mar-2021 and so the March month should show me 25 days and since April is yet to start so it should show as Zero. For the other months since they have passed so they should show all days.
Month Days Passed 2021-01 31 2021-02 28 2021-03 25 2021-04 0
Thanks all,
Rgds, AK
assum Month in column A and Days in column B
Are month "2021-01" text or real date formatted as "yyyy-mm"?
If they are real dates:
use in B2:
=IF(TODAY()>=EOMONTH(A2,0),DAY(EOMONTH(A2,0)),IF(TODAY()>EOMONTH(A2,-1),DAY(TODAY()),0))
or
=IF(TODAY()<=EOMONTH(A2,-1),0,DAY(MEDIAN(TODAY(),EOMONTH(A2,-1),EOMONTH(A2,0))))
or
=(TODAY()>EOMONTH(A2,-1))*DAY(MEDIAN(TODAY(),EOMONTH(A2,-1),EOMONTH(A2,0)))
If they are text like this: "2021-01", try to replace A2 by DATEVALUE(A2&"-1"):
=IF(TODAY()>=EOMONTH(DATEVALUE(A2&"-1"),0),DAY(EOMONTH(DATEVALUE(A2&"-1"),0)),IF(TODAY()>EOMONTH(DATEVALUE(A2&"-1"),-1),DAY(TODAY()),0))
or
=IF(TODAY()<=EOMONTH(DATEVALUE(A2&"-1"),-1),0,DAY(MEDIAN(TODAY(),EOMONTH(DATEVALUE(A2&"-1"),-1),EOMONTH(DATEVALUE(A2&"-1"),0))))
or
=(TODAY()>EOMONTH(DATEVALUE(A2&"-1"),-1))*DAY(MEDIAN(TODAY(),EOMONTH(DATEVALUE(A2&"-1"),-1),EOMONTH(DATEVALUE(A2&"-1"),0)))
Last edited by bebo021999; 03-29-2021 at 11:29 PM.
Quang PT
Maybe:
=iIFERROR(MIN(TODAY()-A2,DAY(EOMONTH(A2,0)),0)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Look promise.
May be MIN then MAX?
=max(min(...),0)
to returns zero for Apr and later months.
Last edited by AliGW; 03-30-2021 at 03:16 AM. Reason: PLEASE don't quote unnecessarily!
Yes, this is better:
=MAX(MIN(TODAY()-A2,DAY(EOMONTH(A2,0))),0)
IF today is counted as a day which appears to be the case in the example, on the 25th the answer is 25, so a slight tweak of the above hard work, I think the below works
=MEDIAN(TODAY()-A2+1,DAY(EOMONTH(A2,0)),0)
If month column is in text format in C2 then copy down
=MAX(MIN(TODAY(),EOMONTH(DATE(LEFT(A2,4)+0,MID(A2,6,2)+0,1),0))-DATE(LEFT(A2,4)+0,MID(A2,6,2)+0,1)+1,0)
If month column is in date format in H2 then copy down
=MAX(0,MIN(TODAY(),EOMONTH(G2,0))-G2+1)
Last edited by kvsrinivasamurthy; 03-30-2021 at 10:56 AM.
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks