excel - Determine the month of a week based on in which month a certain weekday is? -



excel - Determine the month of a week based on in which month a certain weekday is? -

i want user come in year a1.

i want user come in week number a2.

i want user come in weekday (monday, tuesday, wednesday, thursday, friday, saturday or sunday) in a3.

then want a4 calculate month of entered week number weeks counted belonging month depending on if specified weekday in month or not.

what formula work in a4? (if can not solve problem, can give me formula works if specified weekday can thursday, welcome well.

my thought that:

(weeknumber-1)*7 + weekday -> mon = 1, tue = 2.... check day of year [0..365 or 366] , compare offset jan / feb / march... depending on whether year has 29th of feb - can calculated using year.

now have convert code.

edit: after read page reffered to, assume have problem understanding excels date syntax http://office.microsoft.com/en-us/excel-help/date-function-hp010062283.aspx

so lets break code down: happens here?

=choose(month(date(a2,1,b2*7-2)-weekday(date(b2,1,3))),"january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december")

so lets pick first date statement: date (as stated in microsoft's doc) works follows: if month has more 30/31 days leftover days dragged next month. a1 year (yyyy/mm/dd) b2 day times 7 == weeknumber minus offset because first week shall not counted (like week 1 day 3 formula create day 10 otherwise)

** https://support.office.com/en-us/article/weekday-function-a9784177-5c31-4deb-bc9e-d4ab914983ca?ui=en-us&rs=en-us&ad=us

** same month https://support.office.com/en-us/article/month-function-eedac31a-e28c-46fb-b81f-cadb4bc03751?ui=en-us&rs=en-us&ad=us

so monthnames? well, sinmple enumeration , take function in origin convert number between 0...x value @ corresponding place in enum, why months have in order.

excel excel-formula

Comments

Post a Comment

Popular posts from this blog

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* -

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* - i've inherited doc publishing process takes docbook , builds html , pdf output, using apache fop both. project had been started, never completed. lots of tweaking, i've able of doc sets build (20+), except handful, , fail severe code 1 of these 2 conditions: "fo:flow" missing kid elements. required content model: marker* "fo:block" not valid kid of "fo:root" the xml output comes out of docbook , docbook-fop checks out well-formed. the fop error in 1 book, "endecagloss", example, points end of string: <fo:flow flow-name="xsl-region-body" start-indent="4pc" end-indent="0pt"/> here’s log output in case: [java] severe: javax.xml.transform.transformerexception: file:/scratch/publishing/hudson/jobs/build-main-endeca-documentation/workspace/serverdoc/w...
Read more

mediawiki - How do I insert tables inside infoboxes on Wikia pages? -

mediawiki - How do I insert tables inside infoboxes on Wikia pages? - i want insert tables in infoboxes. example, have proteinbox i've developed using infobox template (http://health-and-medicine.wikia.com/wiki/template:proteinbox), , fasta field big fit comfortably in proteinbox. hence collapsible table within box. this tried: {{infobox | row 1 = {| class = "mw-collapsible mw-collapsed wikitable" |- | <!-- table content --> |} }} your problem is, putting nested table within template. pipe characters ( | ) in table syntax collides pipe usage in templates. the commonly used hack around create template called template:! , containing pipe character, , utilize when need set tables, parser functions or other stuff using pipe characters, within templates. table (with every | replaced {{!}} ): {{{!}} {{!}}- {{!}} a1 {{!}} b1 {{!}}- {{!}} a2 {{!}} b2 {{!}}} ...the equivalent of {| |- | a1 | b1 |- | a2 | b2 |} furthermore, hav...
Read more

Local Service User Logged into Windows -

Local Service User Logged into Windows - i created windows service tracks app server user connected through load balancer. it retrieves user logged windows on machine, cross references active directory (ad) business relationship homecoming first name, lastly name , e-mail (for notification through website created project) there 1 scheme on network users remote system, vista system, reason returning values "local service" logged machine, it's weird , security guys impossible "local service" business relationship logged windows. here how user logged windows managementscope ms = new managementscope("\\\\.\\root\\cimv2"); objectquery query = new objectquery("select * win32_computersystem"); managementobjectsearcher searcher = new managementobjectsearcher(ms, query); foreach (managementobject mo in searcher.get()) { _username = mo["username"].tostring(); } // remove domain part username string[] usernameparts = _...
Read more