在Google Sheets公式中添加零。

huangapple go评论62阅读模式
英文:

Add zero in number in Google Sheets formula

问题

我正在寻找Google表格中的一个公式,以查找日期的周数。期望的结果应该是将"S"与周数相连接(两者之间有一个空格)。如果周数小于10,我希望在数据中加入额外的0。

根据不同情况,以下是所需的不同结果:

01/01/2023 → S 52
02/01/2023 → S 01
27/08/2023 → S 09

经过多次尝试,我仍然无法在周数小于10时加入0。你能帮助我吗?

以下是我提供的公式(假设日期位于A1单元格中):

="S "&(IF(WEEKNUM(A1)-1<10;"0"&WEEKNUM(A1)-1;WEEKNUM(A1)-1))

英文:

I am looking for a formula in Google Sheets to find the number of the week of a date. The desired result would be a concatenation of S + the number of the week (with a space between the two). If the number of the week is less than 10, I would like an extra 0 in this data.

Here are the different results desired according to the different cases:

01/01/2023 → S 52
02/01/2023 → S 01
27/08/2023 → S 09

After many attempts, I still cannot incorporate the 0 when the week number is less than 10. Could you please help me?

Here is my formula (assuming a date is in A1):

=&quot;S &quot;&amp;(SI(NO.SEMAINE(A1)-1&lt;10);&quot;0&quot;&amp;NO.SEMAINE(A1)-1;NO.SEMAINE(A1)-1)

答案1

得分: 3

"Can you try:

=&quot;S &quot; & TEXT(WEEKNUM(A1;21);&quot;00&quot;)

在Google Sheets公式中添加零。

英文:

Can you try:

=&quot;S &quot;&amp;TEXT(WEEKNUM(A1;21);&quot;00&quot;)

在Google Sheets公式中添加零。

答案2

得分: 0

使用 TEXT() 函数。尝试 -

="S + " & TEXT(WEEKNUM(A1);"00")
英文:

Use TEXT() function. Try-

=&quot;S + &quot; &amp; TEXT(WEEKNUM(A1);&quot;00&quot;)

huangapple
  • 本文由 发表于 2023年2月8日 16:49:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/75383258.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定