User Profile
NikolinoDE
Platinum Contributor
Joined Jul 08, 2020
User Widgets
Recent Discussions
Re: Publish an Excel Add-in by just submitting the add-in manifest to Microsoft Marketplace
The short answer is yes, the manifest file (an XML file) is the core technical component required for submission. However, you cannot simply "upload the file" and be done. You must go through the Microsoft Partner Center portal, and the manifest must meet strict validation criteria. Since you are self-hosting the React code on your own domain, here is the step-by-step reality of what is required to get your add-in into the Microsoft Marketplace (AppSource). Before you log in to submit, you must have the following ready. If these are missing, the automated validation will fail immediately: HTTPS is Mandatory: Your domain must have a valid SSL certificate (no self-signed certs). All SourceLocation URLs in the manifest must be https://, not http://. Privacy Policy & Terms of Use: You must host two static HTML pages on your domain (or a legal page) stating your privacy policy and terms of use. The manifest requires direct URLs to these. High-Resolution Assets: You need specific PNG icons (128x128, 64x64, etc.) and screenshots of the add-in running in Excel for the store listing. Partner Center Account: You need a verified developer account. There is usually a one-time 5−10 registration fee (for individual accounts) to open a Partner Center account. Preparing the Manifest for Self-Hosting Since you are using the React template and hosting it yourself, you need to ensure your manifest.xml is configured correctly for production Submission Process (Partner Center) You do not send the file via email or a form; you use the Office Add-ins submission tool in Partner Center. Critical "Gotchas" for Self-Hosted React Apps Since you control the server, Microsoft holds you responsible for the following: CORS/CSP: Your web server must be configured to allow the add-in to be iframed inside Excel Online and Windows/Mac clients. Ensure your HTTP headers allow framing from *.officeapps.live.com and *.microsoft.com. Availability: If your server goes down, Microsoft may remove your add-in from the store. Updates: When you update your React code and deploy to your server, the add-in in the store updates instantly for all users. You do not need to resubmit the manifest to Microsoft for code bug fixes (unless you change the UI structure or require new permissions). Telemetry: You are responsible for handling user data (GDPR/CCPA). The manifest asks if you collect data; answer truthfully. To publish right now: Build your React app (npm run build). Deploy to a secure HTTPS server (AWS S3+CloudFront, Azure Blob, Heroku, etc.). Update manifest.xml SourceLocation to point to that HTTPS URL. Create a Privacy.html and Terms.html and link them in the manifest. Log in to Partner Center, create the listing, and upload the XML. Pay the submission fee (if applicable to your account type) and submit for certification. Verdict: You are correct—you only submit the manifest, but that manifest acts as a pointer to your code. Microsoft validates the pointer, but your server hosts the actual payload. I hope this information can help you with your project.3Views0likes0CommentsRe: Only Powerpoint templates in Create in Office.com
I was clearly on the wrong track; what I described earlier isn't entirely accurate, and I apologize. My previous explanation of the "overwriting" behavior was flawed (I'm a bit older than I'd like to admit). If you are seeing Organization Templates + The Default Microsoft Gallery in the web app, but the Default Gallery is missing specific apps (Word/Excel) while PowerPoint works, we need to look at a very specific technical nuance: The "App-First" vs. "Library-First" rendering logic. Here is my revised answer to the question of why PowerPoint works in your create.office.com scenario, but Word/Excel doesn't, even though the settings are correct. The Core Issue: The "CDN Fallback" Failure When you set OrganizationAssetsLibraryType OfficeTemplateLibrary, the web client (WOPI) performs a handshake: It checks the SharePoint library for your templates. It attempts to fetch the Microsoft commercial templates from the public CDN. It merges them. The Bug/Behavior: If the WOPI client detects a "configuration anomaly" in your SharePoint library (even if permissions are 99% correct), it triggers a fail-safe mode. PowerPoint is often resilient enough to ignore the anomaly and show something (your custom PPTs). Word and Excel are stricter. If they detect the anomaly, they suppress the entire Microsoft Gallery to prevent a broken user experience, leaving the user with only the local org templates (if they can read them) or nothing at all. Since you confirmed permissions are "Everyone except external users," the issue is likely one of the following three "Silent Failures": 1. The "Site Collection" vs. "Subsite" Scope The OrganizationAssetsLibrary must be on a Communication Site or the Root Site Collection of a modern team site. The Issue: If https://tenant.sharepoint.com/sites/sjablonen is a subsite (e.g., .../sites/sjablonen/subfolder), the discovery service sometimes fails to enumerate Word/Excel templates correctly, even if PPT works. The Check: Run Get-SPOSite on the URL. Ensure it is a root site. If it is a subsite, move the library to the root or create a new library at the root level. Microsoft's backend is hard-coded to scan root sites more aggressively for Word/Excel than for PowerPoint. 2. The "NoExternals" Permission Block You mentioned: "Everyone except external users have read permissions." The Trap: The create.office.com service acts as an anonymous/guest context initially to probe the library before signing the user in. The Conflict: If "Everyone except external users" is a specific AD group, and "Limited Access" is not granted to the guest/service principal, the probe fails for Word/Excel. PowerPoint's probe is less strict. The Fix: Ensure that SharePoint: AllowAccessForGuestUsers is enabled at the tenant level, OR ensure the specific "Everyone except external users" group includes the NT AUTHORITY\authenticated users claim explicitly. Quick Test: Temporarily add Read access for "Everyone" (All authenticated users) to the specific library only (not the whole site). If Word/Excel templates suddenly appear in the web app alongside the MS Gallery, the issue is strictly the exclusion of external/guest contexts in the permission resolution. 3. Content Type Hub Propagation (The "Ghost" Content Type) Word and Excel web apps rely heavily on the Content Type Hub. The Issue: If the .dotx or .xltx files were uploaded before the Set-SPOTenant command was run, or if they were uploaded via OneDrive sync rather than the browser, the "Word Template" content type might not be properly registered in the hub for the web app to recognize it as a "Template" rather than a "Document". Why PPT works: PowerPoint is more lenient with file headers. The Fix: Download one of your Word org templates. Delete it from the library. Upload via Browser (Drag and drop into the library view). Crucial Step: Before opening, click the file -> Info -> Convert to Content Type (if available) OR ensure that in Library Settings > Advanced, "Manage Content Types" is ON, and the file is explicitly assigned 0x010100... (Word Template). Wait 4-6 hours for the Search/Index to pick up the new content type. The "Nuclear" Verification Step To prove definitively if this is a Service Health issue vs. a Config issue, perform this test: Create a brand new modern SharePoint site (e.g., sjablonen-test). Create a document library. Upload one valid .dotx and one .potx. Run the PowerShell again pointing to this new URL: Set-SPOTenant -OrganizationAssetsLibraryUrl https://tenant.sharepoint.com/sites/sjablonen-test -OrganizationAssetsLibraryType OfficeTemplateLibrary 5. Wait 2 hours. 6. Check create.office.com. If Word/Excel appear here but not in the old library: The old library has a "corrupted" state (permission inheritance or content type issue). If Word/Excel still fail to show the MS Gallery: It is a tenant-wide service bug (Region specific or GCC/Commercial difference) and requires a support ticket. Summary of Correction Yepp: The command does not disable the MS gallery. The likely reality is that Word/Excel web apps are failing the "handshake" with your library due to a Content Type or "External User" permission nuance. Because the handshake fails, the web app suppresses the MS Gallery to avoid errors, leaving you with only the local templates (which PPT can see, but Word/Excel cannot validate fully). Try the "Allow Everyone (authenticated)" read permission on the library specifically for 10 minutes as a test. If the MS Gallery pops up for Word, you've found the permission bottleneck.0Views0likes0CommentsRe: Testflight
C'est un problème très courant et frustrant. Microsoft a changé sa stratégie de distribution ces dernières années, ce qui rend la recherche de liens directs pour Word et Excel séparément presque impossible aujourd'hui. Voici la réalité de la situation et la marche à suivre pour obtenir les accès (et être alerté) sur Mac et iOS. 1. Le changement majeur : La fin des apps séparées sur iOS C'est la raison principale pour laquelle vos liens ne fonctionnent plus ou mènent à des bêtas complètes : Microsoft a fusionné les applications sur iOS. **Avant **: Il y avait une app TestFlight pour Word, une pour Excel, etc. **Maintenant **: Il n'y a plus qu'une seule application : "Microsoft 365" (anciennement Office). Cette app unique contient Word, Excel et PowerPoint. Si vous cherchez "Word TestFlight" sur l'App Store, vous ne trouverez rien. Vous devez chercher "Microsoft 365". Une fois l'app installée, vous pouvez activer/désactiver les modules (Word/Excel) dans les réglages de l'app, mais le bêta-test se fait via l'app mère. 2. Pour Mac (macOS) Sur Mac, la situation est différente. Les applications restent souvent séparées, mais l'accès se fait via le programme Microsoft 365 Insider. Il n'y a généralement pas de lien public "magique". L'accès se fait par tirage au sort ou par liste d'attente via le site officiel. 3. Comment s'inscrire correctement (La méthode officielle) Pour avoir une chance d'obtenir les invitations (surtout quand les places sont limitées), vous devez suivre ce processus précis : Étape A : L'inscription au programme (Obligatoire) Allez sur le site officiel : Microsoft 365 Insider Connectez-vous avec le compte Microsoft (Outlook/Hotmail) que vous utilisez sur votre iPhone/Mac. Choisissez votre plateforme (iOS ou Mac). **Important **: Choisissez le canal "Beta Channel" (Canal Bêta). C'est le seul qui donne accès aux fonctionnalités en avant-première via TestFlight. Le "Current Channel" est la version publique. Étape B : L'application "Feedback" (L'astuce secrète) C'est le meilleur moyen d'être "repéré" par les équipes Microsoft pour débloquer un accès manuellement : Installez l'application "Feedback" (ou "Commentaires") de Microsoft sur votre iPhone/Mac. Connectez-vous avec le même compte. Envoyez des retours constructifs (en anglais de préférence) sur les bugs ou les fonctionnalités manquantes de Word/Excel. Les "Super-utilisateurs" qui envoient de bons rapports de bugs reçoivent parfois des invitations directes par email pour les bêtas fermées. 4. Comment être informé des nouvelles invitations (Stratégie "Commando") Les places partent en quelques minutes. Voici comment ne pas les rater : 1. Suivre le compte Twitter (X) officiel C'est la source n°1. Suivez @Microsoft365Insider. Ils tweetent souvent : "We are opening 1000 spots for Excel Mac Beta, link in bio". Activez les notifications pour ce compte. 2. Le serveur Discord (Non officiel mais efficace) Il existe des communautés de passionnés (comme le Discord "Microsoft Insider" ou des serveurs tech français) où des membres partagent les liens d'invitation dès qu'ils sont postés par Microsoft. Les liens d'invitation TestFlight sont personnels, mais parfois ils postent des liens d'inscription "ouverts à tous" pour une durée limitée. Rafraîchir la page d'inscription Parfois, Microsoft ouvre les vannes sans tweeter. Il faut rafraîchir la page insider.microsoft365.com régulièrement. Si le bouton "Join" (Rejoindre) devient cliquable alors qu'il était grisé, foncez. 5. Si vous êtes déjà "Insider" mais bloqué Si vous êtes déjà inscrit au programme mais que vous n'avez pas accès à la bêta : **Sur iOS **: Allez dans l'app TestFlight. Si vous ne voyez pas Microsoft 365, c'est que vous êtes dans le mauvais "Ring". Allez dans l'app Microsoft 365 > Réglages (icône engrenage) > Aide et commentaires > Basculez vers le canal Bêta. Redémarrez l'app. L'invitation TestFlight devrait apparaître dans l'app TestFlight d'Apple. **Sur Mac **: Ouvrez n'importe quelle app Office (ex: Word) > Cliquez sur votre avatar/icône > Choisissez "Insider" (ou "Bêta"). Si l'option n'apparaît pas, c'est que votre compte n'a pas été tiré au sort. Désinstallez Office, réinstallez-le, et réessayez de vous connecter au programme Insider. **Résumé pour ne plus chercher les liens **: Arrêtez de chercher des liens directs "Word TestFlight" ou "Excel TestFlight" sur l'App Store. Ils n'existent plus. Cherchez "Microsoft 365" sur TestFlight et inscrivez-vous sur le site web du programme Insider en choisissant le canal Beta. Ce texte a également été généré à l'aide de diverses technologies d'intelligence artificielle. J'espère que ces informations vous seront utiles dans vos projets.0Views0likes0CommentsRe: Only Powerpoint templates in Create in Office.com
This is a very common point of confusion for Microsoft 365 administrators, and you've stumbled upon a key difference in how Microsoft handles templates between desktop and web applications. You are correct. The behavior you're seeing is by design. The PowerShell command you ran is the direct cause. When you run this command: Set-SPOTenant -OrganizationAssetsLibraryUrl <URL> -OrganizationAssetsLibraryType OfficeTemplateLibrary You are telling your entire Microsoft 365 tenant: "From now on, for all web-based Office apps (like create.office.com), ignore the default Microsoft template gallery and only show templates from this specific SharePoint library." This is why you only see your organization's templates on the web. The web applications are respecting your central configuration. You can maybe use this workaround to resolve this. The "Best of Both Worlds" The create.office.com service needs to be able to read the files in your template library. When permissions are too restrictive, it falls back to showing only what it can guarantee access to, which in a misconfigured state can sometimes result in an empty or limited view. However, the most common reason for seeing only your templates is that the setting is working as intended, but you want to add the Microsoft ones back. Unfortunately, you cannot simply "add" the Microsoft gallery back. The OrganizationAssetsLibrary setting is designed to be an override, not an addition. The Real Solution for a Hybrid Approach: The intended way to have both is to populate your organization's template library with the Microsoft templates you want your users to have. Download the desired Microsoft templates (e.g., a project plan, a pitch deck) from the public Microsoft template gallery or from a machine where they are available in the desktop app. Upload these .potx, .dotx, and .xltx files into your designated SharePoint library (https://tenant.sharepoint.com/sites/sjablonen/OfficeTemplates). Ensure Permissions are Correct: The library must be accessible by the users. The easiest way is to ensure that "Share with Everyone" or at least a broad security group that includes all your users has Read permissions on the site and the library. The create.office.com service acts on behalf of the user, so if the user can't see the file, the service won't show it. When you do this, users will see your custom templates and the Microsoft templates you've added to the library, all in one place in the web app. I hope this information helps you with your plans.5Views0likes0CommentsRe: Regression: File hyperlinks with #Bookmark no longer work in Office 2601/2602
This is a confirmed regression that has been affecting users on the Current Channel (Preview) specifically around builds 16.0.17425.20000 (Version 2502) and later, persisting into the 2601/2602 builds you mentioned. You are absolutely correct about the mechanism: Office is now aggressively enforcing RFC 3986 (URI Generic Syntax) standards where the hash/pound symbol (#) must be encoded as %23 in the URI path. In previous versions (2409/2412), the Windows Shell handler for .docx/.xlsx was lenient. It would receive a string like C:\File.docx#Bookmark, see the #, and treat it as a "place" inside the file (OLE/COM navigation). In Version 2601+, the internal Hlink.dll or the URI handler is normalizing the path before passing it to the OS. Input: C:\File.docx#Bookmark Normalization: file:///C:/File.docx%23Bookmark Handoff: Excel passes this encoded URI to the default browser (Edge/Chrome) or the Shell. Failure: The browser sees %23 and looks for a file literally named File.docx#Bookmark (where # is a valid filename character) or fails because it cannot access local file:// paths with encoded fragments correctly. The native app (WinWord.exe) never gets the command line argument in a format it recognizes as a bookmark. The "Insert > Link" Manual Fix Instead of pasting the link, use the Insert Hyperlink dialog: Select the cell. Insert > Link > Place in this Document (for internal) or Existing File or Web Page (for external). Crucial Step: In the "Address" bar at the bottom, manually type the # symbol. Do not copy-paste a URL-encoded string. If the file is local, Excel sometimes handles the "Address" box differently than a pasted hyperlink. Registry "Legacy" Mode (Use with Caution) There is a registry key that forces Office to use the older method of handling file URIs, though this is undocumented and may break other things. Open regedit. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Internet. Create a new DWORD (32-bit) Value named ForceShellExecuteForFileLinks. Set the value to 1. Restart Excel. Info about: This forces all file links to go through ShellExecute, which bypasses browser checks entirely. It usually fixes the # issue but might break "Open in Browser" features for SharePoint links. The VBA "Shim" (Recommended for Power Users) You can use a VBA macro to intercept hyperlink clicks, decode the %23 back to #, and launch the file using the Windows Shell API directly. Paste this into a standard module in your Personal Macro Workbook (PERSONAL.XLSB): ' Force Excel to use ShellExecute instead of the broken internal resolver Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _ ByVal hWnd As LongPtr, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String, _ ByVal nShowCmd As Long) As LongPtr Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Dim strPath As String strPath = Target.Address ' Check if it is a local file link with a bookmark issue If InStr(1, strPath, "%23", vbTextCompare) > 0 And Left(strPath, 2) <> "http" Then ' Replace encoded hash back to real hash strPath = Replace(strPath, "%23", "#") ' Cancel the default behavior (optional, prevents error noise) ' Note: This event is post-click, so we can't easily "cancel" the click, ' but we can immediately fix/retry the launch. ' Use ShellExecute to open the file natively ' SW_SHOWNORMAL = 1 ShellExecute 0, "Open", strPath, vbNullString, vbNullString, 1 End If End Sub Until they release a hotfix, the VBA workaround or staying on Version 2412 (Build 18324.20168) are your only stable options if dark mode is a requirement. Version 2412 is the last "sweet spot" build that has Dark Mode without the hyperlink regression. I hope this information has been helpful to you.0Views1like0CommentsRe: Formula Help
Based on your latest update, you've confirmed that the sheets (41 through 46) do exist, which eliminates the most common culprit. The fact that data flows to those sheets but doesn't come back is a critical clue. This strongly points away from a simple typo and toward a scope issue with the named range Rate. What probably happened… You set up Sheet '40' and defined "Rate" (e.g., in cell '40'!$B$5) using the Name Manager. When you created sheets '41' through '46' (likely by copying Sheet '40' or creating them manually), the Name Manager did not automatically create a "Rate" name for those specific sheets. Even though the sheet is named '41', Excel looks for a Named Range called "Rate" scoped specifically to that sheet, doesn't find it, and throws the #NAME? error. It does not automatically borrow the "Rate" from sheet '40'. Check…. The "Name Manager" Audit This is the cleanest way to fix the workbook so you don't have to rewrite formulas. Go to the Formulas tab on the ribbon and click Name Manager. In the list, look for the name Rate. Look at the Refers To column. Does it say =Sheet40!$B$5 (or similar)? The Fix... If you have many sheets, change the Scope of "Rate" to Workbook. OR, if the "Rate" cell is in a different location on every sheet (e.g., always cell B5), you might be better off deleting the Named Range entirely and using direct cell references (see Solution 3). OR, if you must keep the Named Range, you need to ensure every single sheet (40, 41, 42...) has its own "Rate" name defined in the manager. Note: If you copied the sheets, the new "Rate" names might be pointing back to Sheet 40's cells. You must edit them to point to their own sheet. Or use… The "Brute Force" Reference Named Ranges are great for readability, but terrible for copying sheets if not managed perfectly. The most "bulletproof" method in construction estimating is to stop using Named Ranges for single cells and just point to the cell directly. Go to sheet '41'. Find the cell that contains the rate (let's assume it is cell C5). Change the formula in your WBS sheet (G66) to: =IF(D66>0, '41'!$C$5 + $G$18, 0) If you have many sheets, this is tedious, but it eliminates the #NAME? error because Excel never has to "guess" what "Rate" means; you are giving it the exact GPS coordinates.2Views0likes0CommentsRe: Functions and formulars
Place this formula in cell B13: =IFS(B14>=30, "Pink", B14>=17, "Purple", B14<9, "Yellow", TRUE, "") If you are using a version of Excel before 2019, you might not have the IFS function. In that case, you "nest" IF functions inside each other. The logic is identical, but the syntax is a bit messier. =IF(B14>=30, "Pink", IF(B14>=17, "Purple", IF(B14<9, "Yellow", ""))) The VLOOKUP Method (Most Scalable) This is a more advanced but very powerful method, especially if you have many ranges (e.g., 10+ colors). It keeps your logic separate from your formula, making it very easy to update. =VLOOKUP(B14, D:E, 2, TRUE) All formulas in cell B13. I recommend the IFS function. It's the modern, clean, and correct way to solve this, my own opinion 🙂23Views0likes0CommentsRe: Excel array question - spill VOWD based on forecast month
=MAKEARRAY(ROWS(A2:A7), 1, LAMBDA(r,c, LET( fore_month, INDEX(A2:A7, r), SUMPRODUCT(N(COLUMN(B1:M1)<=COLUMN(B1)+fore_month-1), INDEX(B2:M7, r, 0))))) Based on your description, the logic is to sum the values from the first N months (where N equals the "Fore Month" value) for each pair of rows.42Views0likes0CommentsRe: Sensitive titled word doc now appears in company's onedrive and won't delete!
The "Remove" Trick Sometimes the "Delete" button is greyed out, but "Remove" still works. "Remove" unlinks the file from the folder but leaves it in the recycle bin (which you will empty next). Log into the Web Version of OneDrive (do not use the desktop app or File Explorer). Go to the file. Right-click the file. Look for Remove (it looks like a trash can with a minus sign or a broken link). Do not click Delete. If that works, go immediately to the Recycle Bin on the left sidebar and empty it. I hope this helps you20Views0likes0CommentsRe: onedrive.cync.service.exe disable how?
Quick Method Press Ctrl + Shift + Esc to open Task Manager Find onedrive.cync.service.exe in the list Right-click it and select "End task" This stops it right now, but we need to prevent it from coming back Permanent Remove the Service Since the app isn't showing up normally, let's remove the service entirely: Press Windows Key + R, type cmd, and press Ctrl + Shift + Enter (this runs Command Prompt as Administrator) If prompted by User Account Control, click "Yes" In the black window, type this command and press Enter: sc stop "onedrive.cync.service" 4. Then type this command and press Enter: sc delete "onedrive.cync.service" This directly removes the service so it can't run again. I hope this helps you17Views2likes0CommentsRe: Formula Help
A few questions before I get to my proposed solution. Have you actually created a sheet named '41' in your workbook yet? What is the exact name of the sheet tab that contains the data for item 41? (For example, is it just '41', or is it something like '41 - Kitchen' or 'Sheet41'?) Could there be a typo in the sheet name reference? For instance, is there an extra space in the sheet name (like '41 ' with a space after) or in your formula? Additional information such as operating system (Win ver., mac, etc.), storage medium (OneDrive, Sharepoint, Hard disk, etc.), Excel version, would be helpful in order to get an accurate solution. My suggestion corresponds to the information you provided in your message. First:..Rename the sheet OR Copy sheet "40" to create sheet "41" Fix the formula to match the actual sheet name If the sheet is named something like "41 - Bathroom", change your formula to: =IF(D66>0,'41 - Bathroom'!Rate+$G$18,0) Use INDIRECT for more flexibility If you want formulas that automatically adjust, you could use: =IF(D66>0,INDIRECT("'"&ROW(A41)&"'!Rate")+$G$18,0) But this is more complex and might not be necessary for your needs. Try checking if sheet "41" exists first - that's almost certainly the issue! I hope this helps you with your project.22Views0likes0CommentsRe: Time sheet for hours worked
Here's a small attempt, maybe this will help you. That calculates total hours worked with 6-minute increments (rounded to the nearest tenth of an hour). =FLOOR(( (Clock_Out - Clock_In) - (Lunch_Out - Lunch_In) ) * 24, 0.1) Example Breakdown: Clock_In: 8:00 AM (0.3333 in Excel) Lunch_Out: 12:00 PM (0.5) Lunch_In: 1:21 PM (0.55625) Clock_Out: 5:00 PM (0.70833) Calculation: =FLOOR(( (0.70833 - 0.3333) - (0.55625 - 0.5) ) * 24, 0.1) =FLOOR(7.65, 0.1) = 7.6 Ensure cells are formatted as [h]:mm to display time correctly.26Views1like2CommentsRe: Time Zone
As an admin and developer, you've hit upon a common and often confusing issue in Microsoft Bookings. The problem where a booking page set to Central Time (CT) reverts to Eastern Time (ET) stems from the fact that Microsoft Bookings has two separate and independent time zone settings. One setting controls what your customers see (the booking page), and the other controls what you and your staff see (the booking calendar), which is tied to your personal Outlook on the Web (OWA) settings. When these are out of sync, it creates the exact issue you're describing. Ensure your Central Time booking page remains in Central Time for customers, while managing the staff calendar view correctly. Step 1: Set the Booking Page to Central Time (The Customer View) This is the most critical step to ensure your customers see the correct time. Go to Microsoft Bookings in your Microsoft 365 app launcher. In the left navigation pane, select Booking page. Select the specific booking page that is set to Central Time. Click on Change language and time zone settings. Under Time zone, explicitly select (UTC-06:00) Central Time (US & Canada). Crucially, check the box for "Always show time slots in business time zone". What this does: It forces the booking page to display times only in the time zone you just selected (Central Time), regardless of the customer's or your own computer's time zone. This is the key to preventing it from "reverting." Click OK and then Save and publish your booking page. Step 2: Align Your Outlook on the Web Settings (The Staff Calendar View) This step fixes the calendar view for you and your staff. If you want your Bookings calendar to also reflect Central Time, you must change your personal account setting. Log in to Outlook on the Web (outlook.office.com). Click the Settings (gear icon) in the top-right corner. Go to Mail > Calendar. Under the General tab, find the Time zone section. From the dropdown menu, select (UTC-06:00) Central Time (US & Canada). Click Save. Note: Outlook on the web will restart to apply the changes. After this, your Bookings calendar should display appointments in Central Time. Important Caveat: If your organization's IT administrator has locked this setting, you may not be able to change it. In that case, you must use the workaround in Step 3. Step 3: The Workaround for Locked Accounts (Use a Dedicated Staff Account) If you cannot change your primary account's time zone because of admin restrictions, the best practice is to use a dedicated "resource" or "staff" account for the Central Time booking page. Create a new user account in your Microsoft 365 admin center (e.g., "Central-Booking-Staff"). During setup, or by editing the user's profile, set its Time Zone to (UTC-06:00) Central Time (US & Canada). You may need to go into the user's Outlook on the Web settings to confirm this. In the Bookings app, go to the Staff tab. Add this new "Central-Booking-Staff" account as a staff member for the Central Time booking page. Ensure this new staff account is the one primarily associated with the schedule, not your personal account. Now, the booking page's calendar will use the new account's Central Time setting, while your personal account's Eastern Time setting remains untouched. The steps were created by and with AI; I was too lazy to list them step by step myself. Hope this helps you 🙂1View0likes0CommentsRe: Organizational Account authentication problem
This is a known, frustrating issue that typically arises from a token caching mismatch or a broken authentication state within the Excel/Office identity broker. Since it works in Power BI Desktop but fails in Excel (and specifically the "Sign in as different user" button behaves erratically), the issue is almost certainly local to the Excel client's handling of ADFS/WS-Fed modern authentication. Clear the Office Credential Cache (Most Likely Fix) The symptom where the "Sign in as different user" button opens and closes immediately suggests Excel has a corrupted or stale cached token for ce.xxx.com. It tries to use the token, fails, but the UI logic to request a new one is broken. Close Excel completely. Open Control Panel > User Accounts > Credential Manager (or search "Manage Windows Credentials"). Go to the Windows Credentials tab. Look for any credentials related to ce.xxx.com, Dynamics365, or MicrosoftOffice16_Data:adalsso. Remove all of them. Advanced Step: If the above doesn't work, you may need to clear the ADAL cache files. Close Excel, then delete the contents of: %localappdata%\Microsoft\Identity\ADAL (Note: This will force a re-login for all Office apps). Restart Excel and try the connection again. Force WebView2 Repair Repair WebView2 runtime: Go to Apps & Features Find Microsoft Edge WebView2 Runtime Repair Or reinstall it. If your organization recently updated security policies, Excel might have fallen back to legacy auth. You can force it to use the modern ADAL/OWA stack via the registry. Open regedit. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Identity. Create a DWORD (32-bit) Value named EnableADAL. Set the value to 1 (or 2 for "Force ADAL"). Also, check HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Common\Identity\DisableADALatopWAMOverride and ensure it is set to 1 (to prevent the Web Account Manager from interfering with ADFS). Restart Excel. My answers are voluntary and without guarantee! Hope this will help you. Was the answer useful? Mark as best response and like it! This will help all forum participants.4Views0likes0CommentsRe: Macro Dummy
Based on the analysis of your VBA macro code, here's a breakdown of the key issues and how to fix them. Your code searches row 1 but says "Row 27" in the comment for data extraction, Make sure your row references are correct. Your code was searching for "letter L" instead of "row 1" due to lowercase "l". Anyway, here's my attempt at a solution... Sub GetDataByDate() Dim wsReport As Worksheet Dim wsSchedule As Worksheet Dim targetDate As Date Dim foundRange As Range ' Safety: Check worksheets exist On Error Resume Next Set wsReport = ThisWorkbook.Sheets("Report") Set wsSchedule = ThisWorkbook.Sheets("Work Schedule") On Error GoTo 0 If wsReport Is Nothing Or wsSchedule Is Nothing Then MsgBox "Required worksheet missing!", vbCritical Exit Sub End If ' Validate date input If Not IsDate(wsReport.Range("B1").Value) Then MsgBox "Invalid date in Report!B1", vbExclamation Exit Sub End If targetDate = CDate(wsReport.Range("B1").Value) ' Search row 1 (fixed typo) Set foundRange = wsSchedule.Rows(1).Find(targetDate, LookIn:=xlValues) If foundRange Is Nothing Then MsgBox "Date not found in schedule header", vbExclamation Else ' Dynamic row reference (replace hardcoded 27) Dim dataRow As Long dataRow = 27 '← Change this number if needed wsReport.Range("B5").Value = wsSchedule.Cells(dataRow, foundRange.Column).Value MsgBox "Data copied successfully!", vbInformation End If End Sub Added Keys… 1. Flexible Date Handling Added CDate() to standardize date formats during comparison 2. Error Prevention Checks worksheet existence before proceeding Validates date input before search Clear error messages for troubleshooting 3. Maintainability Centralized dataRow variable (change once, update everywhere) Clear comments explaining each step Your Actions… Replace dataRow = 27 with your actual target row number Test with Ctrl+Break to enter debug mode and step through Add Application.ScreenUpdating = False at start and True at end for faster execution My answers are voluntary and without guarantee! Hope this will help you.0Views0likes0CommentsRe: Excel auto-truncating timestamps sub milliseconds
You're right that Excel truncates sub-millisecond data (microseconds and nanoseconds) because its datetime format only supports precision down to milliseconds .You’re running into a precision limitation in Excel, not just a formatting issue. The stair-step graphs are the telltale sign of lost precision. Practical Workaround Instead of double-clicking the CSV (which forces Excel's auto-formatting), set up a proper import that preserves your full timestamps with Power Query Import. Open a blank Excel workbook (don't double-click the CSV) Go to the Data tab → Get Data → From File → From Text/CSV Select your CSV file and click Import CRITICAL STEP: In the preview window, find your timestamp column Click the data type icon (usually says "ABC" or "Date") next to the column header Change it from whatever it auto-detected to "Text" Click Load This brings all your data in exactly as written in the CSV—no truncation, no auto-formatting. Your timestamps like 2025-12-18 00:08:46.730845 will remain intact as text strings. Considerations… If you later convert the text to a datetime format in Excel, it may still truncate sub-milliseconds. Excel's native datetime format only supports up to milliseconds (3 decimal places). Workaround: Keep timestamps as text for storage, and use Python/pandas for analysis where pd.to_datetime() preserves nanoseconds. Use Power Query for storage (import as text) and Python for analysis/visualization. This hybrid approach gives you: Data Integrity: Timestamps remain intact in Excel. Flexibility: Leverage pandas for high-precision calculations/plotting. My answers are voluntary and without guarantee! Hope this will help you.9Views0likes0CommentsRe: Need 1 worksheet to add totals from multiple worksheets in the same file.
I’ve seen proposals for using '*'!A1:A100 to sum across sheets—great creativity! While wildcards are convenient here, they are risky. It is better, and above all safer, to use =SUM(Sheet1:Sheet3!A1) or explicit references to ensure reliability! Excel deliberately restricts worksheet references in formulas to explicit names or contiguous ranges to ensure clarity, security, and performance. The wildcard character * is not supported, as it would lead to ambiguities and risks that contradict Excel's design principles. Microsoft doesn’t explicitly state, "We restrict wildcards in sheet references to avoid ambiguity," but their documentations implies it throughFor dynamic or non-contiguous sums, VBA or structured, defined names are, in my opinion, the most reliable solutions. Wildcards (* and ?) in Excel are safe and powerful when used in specific functions designed for pattern matching. Like Check if a cell contains a substring. Example in SEARCH/FIND for partial text matches =IF(ISNUMBER(SEARCH("*apple*", A2)), "Found", "Not Found") // Case-insensitive =IF(ISNUMBER(FIND("*apple*", A2)), "Found", "Not Found") // Case-sensitive Nevertheless, it's a nice approach, thank you for sharing🙂.17Views0likes1CommentRe: Revenue structure
If your original formula was: =IF(F6="", 0, B6 + (C6 * D6)) Modify it to: =IF(OR(E6="", F6=""), B6, B6 + (C6 * D6)) emanate: Base Fee is in cell B6 Increment Date is in cell E6 Termination Date is in cell F6 Original calculation (when dates exist) is: (Base Fee + (Increment Amount * Months))22Views0likes0CommentsRe: Revenue structure
Based on the screenshot you provided, I can see the exact column layout. The previous formula I gave might have been based on a different column assumption. Here is the corrected, robust solution tailored specifically to the column layout in your screenshot (Columns B through F). Place this formula in cell G3 (which corresponds to Jan 26 for Client A) and drag it right and down. =IF(G$2 < $B3, 0, IF(G$2 >= $F3, 0, IF(G$2 >= $D3, $E3, $C3))) *If your Excel uses semicolons instead of commas, use: =IF(G$2 < $B3; 0; IF(G$2 >= $F3; 0; IF(G$2 >= $D3; $E3; $C3)))). If you says "It's not working," , please check these 3 things: Date Formats: Ensure Row 2 (Jan 26, Feb 26) are actual Dates and not just text. If they are text, the comparison G$2 >= $B3 will fail. Absolute References: Ensure they copied the formula exactly, especially the $ signs. If they drag the formula and the references shift (e.g., B3 becomes C3), the logic breaks. Termination Logic: The formula assumes that if the Termination Date is, for example, 14/11/2026, the revenue for November is 0. If they want revenue for the month of termination, change G$2 >= $F3 to G$2 > $F3. However, standard accounting usually cuts off revenue on the termination date, so >= is safer. "I have adjusted the formula based on the screenshot you sent. The columns are slightly different than the generic description. Please use this specific formula in cell G3 (Jan 26 for Client A) and drag it across and down: =IF(G$2 < $B3, 0, IF(G$2 >= $F3, 0, IF(G$2 >= $D3, $E3, $C3))) This handles the automatic zeroing of future months and the fee escalation without needing to change the formula for next year. My answers are voluntary and without guarantee! Hope this will help you.4Views0likes0CommentsRe: Link doesn't work
The error message shows Excel trying to open a file:/// link to a UNC path with URL-encoded spaces: file:///\\G...\Year%20end%20pack%202024.xlsx%23API!A1 This is almost always a link parsing issue, not a missing file. This strongly suggests a profile-specific Office/URL handler issue, not a global Excel or server problem. Most Likely Cause Excel 365 (newer builds like 2601) sometimes mishandles: URL-encoded file paths file:/// UNC format links FSLogix-mounted profile redirections Especially when the hyperlink includes a cell reference (#API!A1). Workarounds/Fix Recreate the Hyperlink From: file:///\\Server\Share\Folder\Year%20end%20pack%202024.xlsx#API!A1 to: \\Server\Share\Folder\Year end pack 2024.xlsx#API!A1 Or recreate using: Right-click → Link → Existing File → browse to file Do NOT paste a file:/// URL. If this fixes it → your issue is URL encoding handling in that build. If You Want a Permanent Technical Solution If these are auto-generated links (SharePoint, scripts, etc.), modify them so they: Don’t use: file:///\\server\share\file.xlsx Use: \\server\share\file.xlsx In the End, in AVD environments on 2601, the most consistent fix I've seen is: Online Repair Office + disable Office file sync integration. My answers are voluntary and without guarantee! Hope this will help you. Was the answer useful? Mark as best response and like it! This will help all forum participants.3Views0likes0Comments
Recent Blog Articles
No content to show