What is the basis of =NOW() time that shows in Excel Online

MVP

When I publish to Excel online my =now() formula displays a time 13 hours behind local Perth Time (Australia) 

 

Question1:  Any way to force the display in Local Perth time?

 

Question2:  When I open the file in Excel online Dates display as mm/dd/yy  (US Format) even though my original Excel file and my Office 365 preferences show dd/mm/yy.   Is there a way to fix this also?

 

 

14 Replies

Hi Wyn,

 

I guess you have to change your locale and regional settings for your OneDrive in O365 (OneDrive -> gear on top right -> Site settings). I guess you have corporate account.

Hi Wyn, Hi Sergei,

 

as I am curious about an answer for Wyn's questions too, I checked it on my accounts.

 

On my E3 Office365 account, I have in my profile the settings Language = German, Timezone = (UTC+01:00) Amsterdam, Berlin, Rome, ... and as date and time format dd.mm.yyyy hh:mm. If I create a new Excel file within Office 365, I'll get for =NOW() the value 19.04.2017 04:50. However, it's 13:50 actually. So, the time is wrong. I don't know, if I have to change more settings too in Office 365.

 

On my private OneDrive account, I get 19.04.2017 12:50. So, the daylight hour is forgot and this is also wrong. I checked several options on my private OneDrive account and did not find settings for that. However, it's also a little bit confusing there, as there are e.g. settings for the OneDrive Language, my Language and Excel shows me formulas in English (Browser).

 

Best,

Mourad

Hi Mourad,

 

Strange. For O365 E3 under the Language setting do you mean Locale?

Locale.JPG

 

And where did you find such date/time format, here is only one option 12/24 hours format

 

TimeFormat.JPG

We speak about OneDrive site settings, correct?

 

As for the personal O365 - yes, all settings are under your account setting. Here they have no option for the locale, the only way to change it is to change your country.

So, I had to change the settings as Sergei suggested, however my files were in Sharepoint Online rather than OneDrive

 

So I had to go the Sharepoint site page that I needed to change and go to the cog.

 

It appears to be that you have to do this for each individual site (which is a pain).

 

Then Site Information followed by view All Site Settings,  after that it's Regional Settings.

 

Sharepoint Site Information.PNG

 

Sharepoint Regional Settings.PNGSharepoint Site Settings.PNG 

Wyn, maybe it's worth to ask SPO people in there community if it's possible to change regional setting for entire site collection at once. If not from UI perhaps they know some magic powershell script.

 

At least there is no option for entire tenant so far, see https://office365.uservoice.com/forums/273493-office-365-admin/suggestions/6835268-tenant-wide-time-...

 

The reason is the tenant people could be distributes among time zones/regions and they have to have the ability to adjust setting for their sites.

Hi Sergei, Hi Wyn

 

@Sergei: in Office365 E3-Plan, I have the following as shown in the screenhsot, when I go to my profile.

 

2017.04.19-14.49.05.png

 

In OneDrive (Personal Free Account, no Office365), I have only the possibility to choose my country.

 

----------------------------

 

@Wyn: thanks! I have found it. I went to one of my Sharepoint sites (where an Excel Online file loads in) and found the regional settings, which were set to UTC-08:00 PST (Pacific Standard Time, US).

I agree, that's a pain to change all sites. As user I would have expected to have my settings override them. But I am not a Sharepoint expert.

 

Best,

Mourad

 

 

Mourad,

 

Strange, i'm also on E3 but have different interface

Mysettings.JPG

 

Anyway, doesn't matter. Back to Wyns' question, as i know Excel online on business subscriptions doesn't care about your personal account settings, it works in accordance to site settings.

Okay, bit more. I dind't test, but probably that could help.

 

Go to Admin Center -> Sharepoint -> user profiles

Under the People click Manage User profieles

Profile01.JPG

 

Find yourself and under drop down menu  (it's not obvious it is here) select Edit profile

 

profile02.JPG

In huge list of settings find this one

profile03.JPG

 

Chnage on Always use personal setting, click Only me, after that page refreshes and you may select desirable locale for this concrete user. Perhaps some other settings to chnage some way.

 

Sure you have to have admin rights for all that stuff. Again, i didn't test how that will affect Excel behaviour.

 

 

Thanks Sergei

That's useful. I'm thinking there is some risk around some users experiencing different dates if the today() or now() formula is used in the file. That's the issue I came across yesterday. Thanks for your help

I am also having trouble with this. I'm using an Office365 subscription which gives me free OneDrive. So I am often working on documents from different locations - at my office or at the client, etc. So I use OneDrive. It's very handy.

 

I usually use the locally installed Office applications but today I gave a shot at the option to just open my excel workbook - it's a timesheet - online. I noticed that the last entry - which is coded to use Now() if timeout hasn't been punched - shows over hours of work, even though I've just punched in at 9:30am. I figured it's a timezone thing and confirmed it by entering =Now() into a cell and seeing the time. Sure enough - it's 12:16pm here, but the time I get back is 4:16pm! (Goody - almost time to go home! ;)

 

I checked my account setting - the timezone is set properly. Is there any fix for this? I won;t be able to use Excel online if I can't relay on it to execute relative to my timezone.

@Joseph Geretz 

 

Hi Joseph

 

Did you ever find an answer to this problem? I am having the same issue with this formula in both Excel and Sharepoint online =now() and =today() both insist on displaying in PST when ALL my regional settings are set to UTC+12

 

There must be a global tenancy default somewhere but damned if I can find it.

@markh1325 

If we speak about OneDrive for Business,

Click Settings

image.png

Here More settings->Regional settings

image.png

and set your timezone within it.

 

For Sharepoint Site

Settings gear->Site Information->View All site settings (at the bottom of the pane)->Regional settings (under Site Administration group)

@Sergei Baklan 

 

Thanks for the one drive settings. Not sure how I missed those. This appears to have fixed the formula in Excel online

 

I already had all the sharepoint settings set to UTC + 12  =now() =today() still getting wrong timezone in Sharepoint

@markh1325 

i have the same issue from along time, when i reffer to microsoft with many troublshooting over last 4-5 month, and nothing solved.

now i'm searching for somthing to give me the timezone for specific country. or to have somthing to get the timestamp from a list i create.

 

any Idea will Help

 

many thanks

 

Haitham Malak