ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦óÅý5¦ì¼Æªº®É¶¡®æ¦¡¯à¥¿±`¬Û´î

[µo°Ý] ¦p¦óÅý5¦ì¼Æªº®É¶¡®æ¦¡¯à¥¿±`¬Û´î

¥»©«³Ì«á¥Ñ zxcv778899 ©ó 2020-3-24 14:47 ½s¿è

·í­pºâªº®É¼Æ¹L¸U¤p®É®É¡A·|¥X²{¿ù»~
³]©w¡GA1¡BB1¡BC1Àx¦s®æ®æ¦¡¬°¤p®É:¤ÀÄÁ [h]:mm
A1¿é¤J¡G10000:01
B1¿é¤J¡G12000:00
C1¿é¤J¤½¦¡=B:B-A:A
¥¿½TÀ³Åã¥Ü1999:59¡A¦ý·|¥X²{#VALUE!
¸Ó®æ¦¡­pºâ4¦ì¼Æ¥H¤U¬Ò¥¿±`
ºô¸ôª¦¤åÀx¦s®æ¬ÛÃö³]©w±Ð¾Ç ©|¥¼§ä¨ì¯à¸Ñ¨Mªº³]©w
·Ð½Ð°ª¤â¨D¸Ñ

¦^´_ 1# zxcv778899


    https://support.office.com/zh-tw/article/excel-%E7%9A%84%E8%A6%8F%E6%A0%BC%E5%8F%8A%E9%99%90%E5%88%B6-1672b34d-7043-467e-8e27-269d656771c3

¥i¥H¿é¤Jªº³Ì¤j®É¶¡­È  9999:59:59

TOP

¦^´_ 1# zxcv778899
½Ð°Ñ¦Ò¡G
Excel-¾ã²z¦Û½sExcel±Ð¾Ç¤å³¹¶°(~2015/5/24)
https://isvincent.pixnet.net/blog/post/44882566

TOP

¥»©«³Ì«á¥Ñ zxcv778899 ©ó 2020-3-25 10:54 ½s¿è

ÁÂÁ cucu¸òc_clai¨â¦ì¤j¤j´£¨Ñ¬ÛÃö¸ê®Æ

¦]Excel¦³¥i¿é¤J³Ì¤j®É¶¡­È  9999:59:59 ªº­­¨î(µLªkÅã¥Ü5¦ì¼Æ)

¬G¸Ñ¨M¤è¦¡±N¤p®É:¤ÀÄÁ§ï¬°¤ÀÄÁ­pºâ«á¦AÂà¦^­ì¸ê®Æ®æ¦¡
³]©w¡G¨D®É¶¡¤G´î®É¶¡£¸ªº­È¡C²Ä¤@¦C¬°¼ÐÃD¡CA2¡BE2Àx¦s®æ®æ¦¡³]©w¬°¤å¦r(¦]¬°Excel¹w³]ªº¦Û©w¸q¡A·|Åܨä¥L¤é´Á®æ¦¡)
®É¶¡¤@A2¿é¤J7777:10
®É¶¡¤@ªº¤p®ÉB2¿é¤J=LEFT(A2,SEARCH(":",A2,1)-1)
®É¶¡¤@ªº¤ÀÄÁC2¿é¤J=RIGHT(A2,LEN(A2)-SEARCH(":",A2,1))
®É¶¡¤@ªºÁ`¤ÀÄÁD2¿é¤J=(B2*60)+C2

®É¶¡¤GE2¿é¤J17777:20
®É¶¡¤Gªº¤p®ÉF2¿é¤J=LEFT(E2,SEARCH(":",E2,1)-1)
®É¶¡¤Gªº¤ÀÄÁG2¿é¤J=RIGHT(E2,LEN(E2)-SEARCH(":",E2,1))
®É¶¡¤GªºÁ`¤ÀÄÁH2¿é¤J=(F2*60)+G2

¬Û´î®É¶¡I2¿é¤J=TEXT(INT((H2-D2)/60),"00")&":"&TEXT(MOD((H2-D2),60),"00")
­pºâµ²ªG10000:10
(4¦ì¼Æ¸ò5¦ì¼Æ³£¯à­pºâ)

¥H¤W¤p§Ì¤À«Ü¤F¦h¨BÆJ¸Ñ¥X¨Ó¤F^_^
¦pªG¦³°ª¤â¥i¥H²¤ÆÅwªï´£¨Ñ ·P®¦

TOP

¦^´_ 4# zxcv778899

®É¶¡¤@:A2¿é¤J"7777:10"
®É¶¡¤G:B2¿é¤J"17777:20"
C2/¤½¦¡:
=TEXT(MMULT((SUBSTITUTE(A2:B2,":"," ")&"/60")/24,{-1;1}),"[h]:mm")

TOP

¦^´_ 5# ­ã´£³¡ªL

ÁÂÁª©¥D¸Ñµª ·P®¦

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD