Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [google-sheets-dates]

Use for questions related to how Google Sheets handle dates.

1
vote
1answer
11 views

Adding mm:ss times when expressed as TEXT

I have a series of numbers that, for a reason out of my control I can not change, are expressed as plain text strings on google sheets. The numbers are supposed to be minute second. mm:ss. For the ...
1
vote
2answers
17 views

How to make sequential list of months?

I want to fill column names with a sequential list of the last day of each month. For example, 3/31/2018, 4/30/2018, 5/31/2018, etc. Is there a formula that can do this? If cell B1 = 3/31/2018, I ...
1
vote
1answer
12 views

Google Sheets color row if date is closest to today's date

If possible, I would like to have a color schema applied to the table. For instance, the cell that has the closest date to today will be green The cell that has the furthest date to today will be red....
1
vote
1answer
11 views

If a cell equals to this date, then filter through another sheet and grab that cell that has the same date

When you input a date in sheet 1, I want it to automatically grab data from sheet 2 and input text that matches that date back into sheet 1
1
vote
1answer
18 views

IMPORTRANGE from a separate sheet based on closest date to today's date

Sheet1 will have data with different past dates Sheet2 - I want to import data from the 1st sheet and find the closest date to today's date. https://docs.google.com/spreadsheets/d/...
1
vote
1answer
21 views

Google Sheets Conditional Formatting not working for all cells

This is my first question using StackExchange, so please bear with me. I am making a spreadsheet for tracking due dates for training. Sheet 1 shows due dates that people with view only capabilities ...
1
vote
1answer
14 views

Date highlighting of a cell, separated by weeks, given only a starting date and a cell range

I am trying to build a "life calendar" similar to this - https://labs.coruscantconsulting.co.uk/life/weeks/ - in Google Sheets. I have the manually designed sheet finished, but want to add the ...
1
vote
1answer
14 views

How do I refer to data in a column under this month?

In the below screenshot, I'm trying to get B42 to show the value of whichever cell on row 40 is under the current month's column: As the dates are automatically generated using a transpose(...
1
vote
1answer
17 views

Google Sheets' MONTH function on an empty cell returns 12

Is this an expected behaviour that month(ref) returns 12 when the ref is an empty cell? I would have thought that this is a serious bug. Am I correct? No mention of that behaviour in the official ...
1
vote
1answer
15 views

Looking for a way to get the current date represented as a numbered month with decimals

Essentially I'm hoping to find a formula that would return the current date as a month in decimal form. So if it's October 15th, the number returned would be 10.48. September 10th would come back as 9....
0
votes
0answers
16 views

Issue with SUMIFS pulling data from QUERY across multiple sheets

I would like to sum values from a QUERY generated list in the sheet I/S/N/W to the sheet BUDGET for determining a value based on the date in BUDGET!B8. The function in BUDGET!B8 is =MONTH(TODAY()). ...
0
votes
1answer
19 views

What function formula should I use to calculate my salary so that I don't have to differentiate between Friday and weekend manually?

These are the formulae I am currently using where I have to manually sort out if its Friday or weekend by using conditional formatting to bring attention to the minority that needs a different formula....
0
votes
1answer
12 views

Boolean test of whether a time is before or after midnight in Google Sheets?

I'm having a job trying to create a Google sheet which calculates the difference between the time I fell asleep and the time I woke up. Here's what I want to happen: Sleep time Wake time ...
0
votes
0answers
15 views

Looking to open a timesheet in Google sheets and auto seek the current date [duplicate]

My employer has recently transitioned from excel to Google sheets as our timesheet platform, and we're finding that every time the doc is opened or recovered from suspension, it resumes at the top. ...
1
vote
1answer
20 views

Link to a date cell in QUERY criteria

I have the following formula and it works fine =unique(query(ARRAY_CONSTRAIN(ARRAYFORMULA(QUERY( {Database!A1:AB, (Database!N1:N)-(Database!M1:M)}, "where Col9 <> '' AND Col1 >= date'2019-...
1
vote
1answer
28 views

Time not formatting as expected in Google Sheets

I imported a report to Google Sheets and the duration column shows 7 h 37 m or 14 h 5 m a similar format. When I replace h with : it still does not properly format to time duration and so I cannot ...
0
votes
1answer
17 views

Google Sheets Pivot Table, Summary by Day

I have a sheet with 2500 dates & times of when customers have placed their order. I want to do a heatmap or a bar graph to show which days of the week are the busiest. I rounded the exact times ...
0
votes
1answer
28 views

How do you get current date to be added to a cell when a check box is checked in another cell

I want to get the current date added into a cell when a tick box is checked in another cell. I currently have another formula in the date cell which changes its colour when the tick box is checked, ...
0
votes
1answer
12 views

Changing cells formatting based on another cells date

I've been looking and can't quite find the answer I'm looking for. I have a spreadsheet with columns Monday through Saturday with data lining up underneath. I would like the current day to highlight ...
1
vote
1answer
35 views

Query select all values of specific month

A have a table where Column A has dates. I want to get all values of a specific month I have the following query for May for example =query(Database!A1:AD,"Select A,E,F,U,V,W,X,AB,AC where Month(A) =...
1
vote
2answers
102 views

How to return values in a formula, from rows of latest 4 nonconsecutive dates recorded

Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of nonconsecutive dates. =ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0), {...
1
vote
1answer
9 views

Set certain days of the week on repeat [closed]

I'm making an attendance sheet for my team, how do I make only certain days like Mon Tue Wed appear in the top column?
0
votes
0answers
8 views

sSheet DateTime format

I am using AppSheet to create a Timesheet App and on the App, I have to use the DateTime format to be able to calculate overtime and regular hours correctly. My gSheet DateTime format returns a start ...
2
votes
1answer
24 views

Add missing dates to list of dates with values

Current Data: 9/20/2018 6 9/22/2018 3 9/23/2018 1 Desired Output: 9/20/2018 6 9/21/2018 9/22/2018 3 9/23/2018 1 I would like to have the missing dates added to Column A while the ...
2
votes
1answer
16 views

How do I create multiple time zones by using the NOW formula?

I'm trying to create 5 cells with different time zones: CDT MDT MST PDT EDT I am in MST, so I obviously get my current time by doing NOW() How do I add hours to that? I tried NOW()+(2,0,0) But ...
1
vote
1answer
23 views

Insert a weekday in Google Forms' date selector

We have created a Google form that works perfectly, there is only one issue, and that's the DATE selector, it sends the DATE, but we would like to receive the DATE and the WEEKDAY in the form we ...
1
vote
1answer
80 views

Query(Importrange) or Query with dates

I am trying to show or limit what is seen to just entries made in 2019. I found a few places that referenced this and have tried the following without success. =query(IMPORTRANGE("1bSyor-...
0
votes
1answer
14 views

Change Colors of Cell per Day based on Date in Cell

I'm new to Google Sheets and need help with conditional formatting. I have Column C, RESPA DATE, that has a date that varies for each borrower. I want to change the color of the cells in Column D, ...
0
votes
1answer
15 views

INDEX + MATCH to return the greatest of all matching values

I'm looking for the most recent date corresponding to a particular name. Dates are not necessarily ordered. I can't sort the target, I need it to work in any order. Example: When was [Roberto]'s most ...
1
vote
2answers
19 views

Change cell colour based on another cell containing text (day name)

I am new to Google Sheets and trying to create a custom formula in conditional formatting to make the fill colour of a cell change based on another cell containing a specific text. I want to change ...
0
votes
1answer
6 views

Creating a formula based on TODAY's day

I have column A (list of stock prices at close of day) A1=4/1/2019 | stock price A2=4/2/2019 | stock price A3=4/3/2019 | stock price each day will be added to the sheet automatically I want to take ...
1
vote
1answer
32 views

Subtract 8 hours from time, if time is more 8 hours

I am trying to create a Timesheet using Google Sheet but this formula =IF((F8>8),(F8-8),"0") returns zero even on cells with Total Hours greater than 8 I have set both Formats to Duration
0
votes
1answer
26 views

Google Sheets adding weird number in 11th decimal place

Can anyone explain this? I have 18 hardcoded numbers - that is, typed in, not as a result of a formula. [B1 thru B18] I have a cell [D1] that sums those 18 numbers. I have eleven other hardcoded ...
0
votes
0answers
16 views

Auto date cell when opposite cell is populated - problem with script

This question has been asked here and I'm posting a new post because I couldn't figure out how to reply to that one. I installed the script and saved it but no date appears in my sheet when I insert ...
1
vote
4answers
74 views

How to add every Sunday in sequential order?

So I tried using the simple formula: =date(2019,3,10) =date(2019,3,10)+7 =date(2019,3,10)+14 =date(2019,3,10)+21 ... etc. But when I highlight the first top 3-4 cells to drag down and formulate ...
0
votes
2answers
17 views

Extract and graph information from a days-hours-minutes-seconds format [closed]

I'm working in Google Sheets, and I have data for race finish times in "8d 3h 40m 13s" format and I'm want to extract that information so I can plot it in a chart. Any ideas for how to achieve this? ...
1
vote
3answers
1k views

Convert Text String into Date/Time Value

I'm fetching data into a Google Sheet. The date column contains multiple cells with the following text string pattern: Mon Mar 11 19:05:32 +0000 2019 The pattern always repeat itself: [day] [month] ...
0
votes
3answers
82 views

Duration to seconds calculation

There are 3 columns in my google sheet with heading Start time (col C), End Time (col D) and time taken (col E). Column E is arrived by finding the difference between col D and col C. Now when I try ...
0
votes
1answer
95 views

How do I calculate average duration in Google Sheets Result from years:months:day

All I'm trying to do is to get an Average Duration to result in Google Sheets to the average duration from a set of duration years:months:days I calculate DURATION using this formula: =INT((K3-B3)/...
1
vote
1answer
42 views

Date fields that refuse to be parsed/formatted

I have some very mysterious date fields in a huge (600 000 records) CSV file which absolutely refuses to behave. They come up aligned left, and conditional formatting shows that they cannot be seen ...
1
vote
1answer
94 views

Calculate Work Hours

Given work hours for each weekday (let's say 9:00:00-17:00:00 Monday through Friday), a start timestamp (e.g., 1/15/2019 6:15:15) and end timestamp (e.g., 1/21/2019 14:35:10)... How can I get the ...
1
vote
2answers
32 views

Query using date range doesn't work for specific data

I have a spreadsheet (URL below). I've set up a query to make sure I understand how to do a query with a date range. Then I tried to create a similar query on the "real" data. The "Week 1" worksheet ...
0
votes
2answers
24 views

How to compare duration to another duration using IF statements

=IF(TIMEVALUE(H2)<=TIMEVALUE("00:01:00.000"), 150, TIMEVALUE(H2)<=TIMEVALUE("00:01:30.000"),100, IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:00.000"), 75, IF(TIMEVALUE(H2)<=TIMEVALUE("00:02:30....
0
votes
4answers
44 views

Show a Running Target Based on Today

I'm creating a running target from a quarterly Total divided by known trading days Current formula in I4 is =IF(H3<>"",I3+O$30,"") H3 = Yesterday's Sales Numbers I3 = Yesterdays Running Target ...
1
vote
1answer
64 views

Sum all values with same month and category

So I have this table where I'm writing down my daily expenses. On one cell I choose the date, the next cell I choose the category and in the third, I insert the amount of money spent. On another sheet ...
0
votes
3answers
43 views

Picking the Total from the Next Week

I have the next year mapped out (one line for each day) and have the week subtotal at the end of each week (which is a Friday). I am wanting the Next weeks subtotal to show in another cell for that ...
1
vote
1answer
121 views

MIN and SUM functions in Google Sheets for Time

I'm trying to use the MIN and SUM function but the answer always comes back with either 0, ERROR or !DIV/0. The format I want to have for my times are for example: 0.03, 10.56, 1:13.00. =MIN example:...
0
votes
1answer
27 views

How to output end task time base on start time, duration and working time in Google Sheets

I'm using a spreadsheet to perform calculations and I'd like to make a task management file. For example: Working Time: 8:00 ~ 17:00 Lunch Break: 12:00 ~ 13:00 +- Start Time -+- Duration -+- End ...
1
vote
1answer
629 views

Convert inputted time duration to minutes:seconds [duplicate]

I'm working on this spreadsheet, and I wish to convert the data in columns C, D, and E from hours and minutes to minutes and seconds. For example, an inputted 7:08 turned into 07:08:00.000 (hours and ...
0
votes
1answer
123 views

How to make date automatically display only day number and suffix (1st, 2nd, etc.)?

Title explains it. I need to know how to create a custom number format in which a cell would display only the date and suffix of a full date entered into the cell example:1/05/2019 -> 5th I have ...