New answers tagged google-sheets-dates
0
To add a number of workdays to a date (excluding weekends), use WORKDAY(start_date, num_days, [holidays])
0
SOLVED: =ArrayFormula(AVERAGE(FILTER(B2:B-A2:A,C2:C="A",B2:B>=DATEVALUE("2020-1-1"),B2:B<DATEVALUE("2020-4-1"))))
1
You can use the following formulas:
For 2020 use in cell D2
=IFERROR(AVERAGE(ArrayFormula(IF(YEAR($B$2:$B$37)=2020,($B$2:$B$37-$A$2:$A$37)))),0)
You will get a number like 4.475757576. Format the cell as Duration to get the hours (if not interested leave it as is).
Following that, you can use the following formula in cell D3
=INT(D2)&"D "& ...
1
This is an old question, but I want to share my way of solving it (In case someone end here without answer).
Format> Number> More Formats> Custom number format
In there you write the following:
hh":"mm":"ss","000
Remember to write exactly this format, even if the hours, minutes or seconds are double ceros (00) so an ...
1
You can use the TEXT function in your formula:
=TEXT(COUNT(J8:J11)*2/24,"H:mm")&" / "&TEXT(SUM(K8:K11), "H:mm")
0
EDIT:
(following OP's comment)
...to include a custom start and end date which I know I didn't ask for based on your reply
=SEQUENCE(1,B2-A2,A2,1)
Please use the following formula:
=SEQUENCE(1,18,"2018-4-13",1)
More about SEQUENCE
1
Maybe: The From date could be hiding a time element that is used in the calculation, but is not included in the display. For example, if you change your from date to be 2020-3-5 02:18 then it will change the calculated bill to €47.23
Top 50 recent answers are included
