All Questions
101
questions
0
votes
3
answers
636
views
How do I get the sum of hours:minutes in MS Excel in the status bar and in a formula if the cells come in with a text format?
I know that there are questions around that are much the same, like Sum the number of hours in Excel, which asks you to format [h]:mm, which I do and it still does not work.
Also not so far is How do '...
- 2,423
1
vote
1
answer
551
views
Date format issue
I export some files from a network as CSV and then I convert that file as excel file there is one column in excel file named-as (dates) now when I apply the filter on it the filter is not slowing ...
- 11
0
votes
1
answer
1k
views
SUMIF all times between a start and end time of each day
I have an interesting one which I am really struggling to resolve and feel this should be possible using SUMIFS somehow!
I have the following (as per the image):
I have a number of tasks to complete ...
- 3
0
votes
1
answer
1k
views
Removing date from datetime (VBA Excel)
I need to delete the date from mulitple rows with a datetime value. Or extract the time to another cell.
For example:
A1 = 3/11/2022 10:20:00
A1 after macro = 10:20:00
or
A1 = 3/11/2022 10:20:00
A2 = ...
- 1
0
votes
1
answer
616
views
Increase time by 15 minutes by 4 rows per 'time slot'
I'm trying to create a list of certain times for people to sign up in a form.
I did the following by hand, but I'd like to use a formula for it. Looked over the internet for iteration formulas for ...
- 143
0
votes
1
answer
2k
views
Excel Date Timezone Conversion [duplicate]
I have a spreadsheet from a system that outputs the date and time format like this:
15/09/2021 04:29 AM
The format of the field/cell is Custom:
dd/mm/yyyy hh:mm
How do I convert the output so that ...
1
vote
2
answers
137
views
12 In 24 working hours
How do I calculate the number of hours worked within 24 hours?
If the shift starts at 6 AM and finishes at 2 PM with a half an hour break, and the shift the following day starts at 4 AM and finishes ...
- 13
0
votes
2
answers
814
views
=DATE() won't increment DAY when I try to drag or fill the other cells
I'm working on an excel file to track attendance. The excel sheet should have the flexibility of dynamically changing the year of dates. The user can enter the current year in a cell and it should ...
- 101
1
vote
2
answers
1k
views
Excel won't recognize timestamp in format of "m/d/yyyy h:mm:ss AM/PM"
I have a column of timestamps (strings) formatted like so:
This can be deduced to the following format: m/d/yyyy h:mm:ss AM/PM
I'm trying to get them to be formatted into dates but Excel refuses for ...
- 115
0
votes
6
answers
1k
views
Excel date convert from Mar-29 to 29/03/2020
Simple question that I have not been able to find an answer to.
I have some data that doesn't have years and is formatted as Mar-29, as I have pasted into Excel, but Excel converts this into data ...
- 796
-1
votes
1
answer
7k
views
How do i remove date from a date and time from an excel cell
I have a very huge sheet with date and time values. I am exporting the calendar start date and end date into excel from outlook. I just want the time value in the field. Upon searching i found an ...
- 1
1
vote
1
answer
891
views
Determine number of non-Sundays in a part of a month
Calculate number of Sundays in the month asks
how many Sundays there are to the end of the month.
Two users proposed very similar answers that I liked and worked very well to determine how many ...
- 13
1
vote
3
answers
13k
views
Excel =TIME() with values over 24 hours displays incorrectly
I'm trying to display times over 24 hours in Excel by using =TIME() but when the values get over 24 hours they display incorrectly. For example I have =TIME(27;42,81;49) which should be displaying 24:...
- 113
0
votes
1
answer
348
views
Excel Formula : Find out time spent in specific time ranges
Trying to work out a slightly more complex overtime worksheet here:
Employee gets paid:
1x hourly rate during weekdays (Mon-Fri) during 08:30 to 17:30
1.5x hourly rate during weekdays (Mon-Fri) ...
- 143
0
votes
2
answers
10k
views
How to get time difference from cells formatted as dd/mm/yyyy hh:mm in Excel?
I have two columns of dates and times, formatted as Custom dd/mm/yyyy hh:mm.
A third column is =C3-B3, =D3-D3, =x3-x3, etc.
As you can see, it does not tell the difference in days, hours and minutes,...
- 23.1k
1
vote
1
answer
239
views
Reverse cell formatting, how to explain a certain datetime format to Excel
I am aware that Excel can modify the appearance of a cell content, using "Format cells". When I enter "20000" in a cell, and I use "Format cells", more exactly "Date", Type "14/03/2012", I see "03/10/...
- 2,283
1
vote
4
answers
20k
views
Convert number of minutes to time in Excel hh:mm format
I have a set of data that is represented in minutes. This data needs to be converted to hours and the remainder to minutes.
For example, in column A I have 120 and in Column B I need to present the ...
- 21
1
vote
1
answer
82
views
How can you extract the number of hours until a date?
I have dates in a spreadsheet formatted in this format: 5/10/2017 2:00:00 PM
There is a whole column of dates like that. Formatting shouldn't matter, but I want it in that format as this is a shared ...
- 2,546
0
votes
1
answer
452
views
Exported duration times come up at time of day value in excel, any solutions?
I have the problem with a sheet I downloaded. Right now, online on its platform it displays the number correctly 0:31 (duration of a call) but when I export the list to excel for tracking, it comes up ...
- 1
0
votes
1
answer
197
views
Excel Formula for auto date/time subtraction
I want to do the following in cell K14: If cell D14 contains C17, take cell F14 and subtract 3 hours and 45 minutes.
The date/time format I am using is a Julian Date/military time, so 16271/1845 ...
- 1
3
votes
4
answers
955
views
Determining whether specified time is within 15mins of target time in Excel
I’m trying to create spreadsheet to monitor the timeliness of backups executed. Backups must be executed within 15 minutes of the target time, in order to be considered compliant. The raw data ...
- 253
0
votes
1
answer
9k
views
Excel Start Date with specific start Time
Date and time data is imported into excel from an outside source (excel see the imported data as Text so column Q is converting to date/time that excel will recognize).
I am using multiple formulas ...
- 113
0
votes
2
answers
266
views
Subtracting hours
I'm working on a time tracking sheet. I have a weekly total column with the formula of =SUM(I4:I8). This entire column is formatted as [h]:mm. The current value is 31:49. I want to calculate how ...
- 1,943
1
vote
1
answer
2k
views
Calculating the average Interest Rate using a range of date inputs in excel
I need to calculate the average interest rate for a calendar year, based on a table of exchange rates which change at various dates.
The table simply shows the start date and end date of the set ...
- 55
0
votes
1
answer
75
views
Formula stopped working after changing date field to calculated
Why do I get different results for B2 and C2?
Here are the two formulas:
0:00 = =SUMIFS(tblTrack[Spent],tblTrack[Time Ended],">="&E9)
6:32 = =SUMIFS(tblTrack[Spent],tblTrack[Time Ended],">...
- 1,943
0
votes
1
answer
84
views
General questions about entering minutes into an Excel worksheet
I'd like to be able to enter minutes into Excel cells and have another cell that totals the minutes. I have no problem converting minutes into hours. However, when I enter information, I don't want ...
- 1
0
votes
1
answer
73
views
time format in Excel [duplicate]
How to get desired output in MS Excel?
note that the outage duration is in mins
Start time Outage Duration (in Mins) DESIRED OUTPUT
3:12 AM 60 4:12 AM
4:05 PM ...
- 1
0
votes
1
answer
691
views
Subtract certain hours from a time in Excel
I want to know the time when 12 hrs and 30 mins is subtracted from a time let us say 10:00 AM(it should display 9:30 PM). But since Excel, by default, stores this date as 1st Jan 1990, I am getting a ...
- 1
3
votes
1
answer
2k
views
Work out Start Time + Hours with working days and times
I have the following problem:
In G4 I have 19/01/2016 10:00
In F4 I have the value 200 (units)
In E4 I have the value 38 (minutes)
I want to calculate G4 + (E4*F4) i.e. 19/1/16 10:00 + 7,600 ...
- 31
0
votes
1
answer
391
views
How do I calculate the total busy time between several start-end dates
I have a table like this:
Event Start End
1 1/1/2015 10:00 1/1/2015 10:05
2 1/1/2015 10:02 1/1/2015 10:07
3 1/1/2015 10:10 1/1/2015 10:12
I want to ...
- 1