Hitung sisa gaji dalam setahun

Saya ingin menghitung jumlah hari itu

  1. Terjadi antara sekarang dan akhir tahun (yaitu 31 Desember), dan
  2. Itu jatuh pada tanggal 15 atau hari terakhir setiap bulan (30 untuk April, Juni, September, November; 31 untuk Januari, Maret, Mei, Juli, Agustus, Oktober, Desember; 28 untuk Februari).

Apakah ada cara untuk menyelesaikan ini?


excel excel-formula worksheet-function
person Anna Lam    schedule 08.08.2012    source sumber
comment
Jika suatu bulan memiliki 31 hari, apakah Anda masih ingin memasukkan tanggal 30 pada bulan tersebut? Juga, bagaimana dengan bulan Februari?   -  person Doug Glancy    schedule 08.08.2012
comment
bisakah Anda menggunakan VBA atau hanya berfungsi?   -  person KMC    schedule 08.08.2012
comment
Yap, tanggal 31 Desember sudah termasuk. Saya ingin menggunakan fungsi Excel murni.   -  person Anna Lam    schedule 08.08.2012

Jawaban (3)


arrow_upward
6
arrow_downward

Sepertinya ini mungkin berhasil (walaupun menurut saya ini mungkin bisa dirapikan dan saya yakin ada cara yang lebih baik...)

=(12-MONTH(TODAY()))*2 
+ IF(DAY(TODAY())<15,2,
        IF(DAY(TODAY())<DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)-1),1,0))

(12-MONTH(TODAY()))*2 : Dua hari untuk setiap sisa bulan penuh

Ditambah 2 hari jika sebelum tanggal 15

or

Ditambah 1 hari jika tanggal 15 atau lebih dan bukan hari terakhir pada bulan berjalan

person Tim Williams    schedule 08.08.2012
comment
+ 1 Yang bagus untuk semua akhir bulan. Mengembalikan 10 sebagaimana mestinya. :) - person Siddharth Rout; 08.08.2012
comment
Ini bekerja seperti mimpi. Saya mengubah kurang dari menjadi kurang dari atau sama dengan (baris terakhir contoh kode Anda) sehingga saya dapat memperhitungkan hari ini pada tanggal 15. - person Anna Lam; 08.08.2012

arrow_upward
2
arrow_downward

Varian yang menghindari rumus array.

1. For your question as asked

=SUMPRODUCT(--(DAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+ROW(INDIRECT("1:"&DATE(YEAR(NOW()),12,31)-TODAY()+1)))={15}))+(13-MONTH(TODAY()))
= 10

2. For my initial interpretation which was to count any days corresponding to a certain day of the month

=SUMPRODUCT(--(DAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+ROW(INDIRECT("1:"&DATE(YEAR(NOW()),12,31)-TODAY()+1)))={15,30}))
=10

sedangkan

=SUMPRODUCT(--(DAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+ROW(INDIRECT("1:"&DATE(YEAR(NOW()),12,31)-TODAY()+1)))={15,31}))
=8

Dan

=SUMPRODUCT(--(DAY(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))+ROW(INDIRECT("1:"&DATE(YEAR(NOW()),12,31)-TODAY()+1)))={15,30,31}))
=13

Untuk mengubah hari yang diperlukan cukup ubah komponen {15,30} yaitu ={1,2,12,15,31} untuk menghitung semua hari yang jatuh pada tanggal 1, 2, 12, 15 dan 31 dst.

Rumus ini akan menangani tahun kabisat

person brettdj    schedule 08.08.2012
comment
+1 Penggunaan SUMPRODUCT yang bagus. Rumusnya mengembalikan 8 sebagaimana mestinya. - person Siddharth Rout; 08.08.2012
comment
brettdj. Ini memberikan hasil yang tidak terduga untuk {15,30,31}. Seharusnya mengembalikan 10 atau saya melewatkan sesuatu? - person Siddharth Rout; 08.08.2012
comment
@SiddharthRout Seharusnya 13 - yang mana :) (semua bulan dari Agustus - Desember memiliki setidaknya 30 hari, jadi 8 (dari pertanyaan sebelumnya) +5 = 13) - person brettdj; 08.08.2012
comment
Ha Ha Ha... Disini masih jam 06.39 :D - person Siddharth Rout; 08.08.2012
comment
Ada apa dengan -- di rumusnya? - person Enigmativity; 08.08.2012
comment
@Enigmativity corercion untuk mengubah TRUE menjadi 1 dengan mengalikannya dengan -1*-1. - person brettdj; 08.08.2012
comment
Mungkin ada beberapa kondisi batas yang tidak berfungsi. Misalnya. dalam rumus 1, NOW() pada 15 Des 2012 menghasilkan 1 (seolah-olah tanggal 15 tidak dihitung), tetapi NOW() pada 31 Des 2012 juga mengembalikan 1 (seolah-olah tanggal 31 harus dihitung). Tidak jelas apakah OP ingin memasukkan tanggal saat ini ke dalam hasil, tetapi sepertinya hasilnya tidak bergantung pada apakah TODAY() adalah EOM atau hari pertengahan bulan yang sewenang-wenang. - person andy holaday; 08.08.2012
comment
@andyholaday Formula 2 yang lebih fleksibel mengecualikan kondisi batas untuk 31 Des 2012 (yaitu mengembalikan 0). Saya akan memperbarui bagian non-array dari rumus 1 untuk konsistensi. Anda menyampaikan poin yang adil bahwa kondisi tanggal saat ini seharusnya sudah ditentukan - person brettdj; 08.08.2012

arrow_upward
0
arrow_downward

Ini harus berfungsi kapan saja, kapan saja, di versi Excel apa pun hingga tahun 2078 (lebih lama di Excel 2007 atau lebih baru).

=SUM(IF(DAY(ROW(OFFSET($A$1,TODAY(),0,DATE(YEAR(TODAY()),12,31)-TODAY()+1)))={1,16},1,0))

Catatan Saya memeriksa hari = 1 atau 16 dengan offset 1 hari (idenya adalah untuk menyiasati nilai DAY() akhir bulan yang bervariasi).

{Rumus array... Tekan Ctrl+Shift+Enter untuk melakukan}

[tambahan]

Jika Anda tidak ingin memasukkan hari ini (misalnya, hari ini tanggal 15 Agustus), gunakan ini sebagai gantinya:

=SUM(IF(DAY(ROW(OFFSET($A$1,TODAY()+1,0,DATE(YEAR(TODAY()),12,31)-TODAY()+1)))={1,16},1,0))

hal. Saya menguji semua tanggal mulai hari ini hingga hari kabisat 2016 dan seterusnya dan hasilnya berhasil. Yang dilakukan hanyalah menguji nomor baris yang diperlakukan sebagai serial tanggal dengan fungsi DAY() untuk melihat apakah nomor tersebut 1 atau 16, tetapi serial tersebut diimbangi dengan +1 sehingga benar-benar memeriksa apakah DAY() adalah [hari terakhir bulan apa pun] atau 15. Jika hasilnya benar, tambahkan 1, jika tidak tambahkan 0.

[tambahan lainnya]

Berikut adalah versi non-array yang berfungsi dengan cara yang sama:

Termasuk hari ini:

=SUMPRODUCT(N(DAY(ROW(OFFSET($A$1,TODAY(),0,DATE(YEAR(TODAY()),12,31)-TODAY()+1)))={1,16}))

Tidak termasuk hari ini

=SUMPRODUCT(N(DAY(ROW(OFFSET($A$1,TODAY(),0,DATE(YEAR(TODAY()),12,31)-TODAY()+1)))={1,16}))
person andy holaday    schedule 08.08.2012