Please consider registering
sp_LogInOut Log Insp_Registration Register
Register | Lost password?
Advanced Search
Forum Scope


Forum Options

Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Feed Topic RSSsp_topic_old
Help on Google Spreadsheet
August 28, 2011 - 5:53 am
Member Since: March 4, 2011
Forum Posts: 12
sp_UserOfflineSmall Offline

Hello guys, I am back with a problem. It is on google spreadsheet.

Say you have got 3 cells named A, B and C

A = In time
B= Out time
C = B-A

for google spreadsheet A and B cells must be filled as the format [b:ulutni3f]hh:mm:ss am/pm[/b:ulutni3f]. Now whenever the sum of those hours become 25 (say) then the result shows 1:00:00. But I want to show the result as 25:00:00.

How can I do that?

Jim Hillier
September 2, 2011 - 11:35 am
Member Since: August 9, 2011
Forum Posts: 2705
sp_UserOfflineSmall Offline

Hi Raihan - Sincere apologies for the lack of any reply here mate. We haven't been ignoring you, our resident expert in all things 'Office', the lovely Carol, is currently indisposed.

If you have found an answer to this issue please let us know the solution. Otherwise please hang tight and Carol will be back on board with the right advice shortly.


Montreal, Canada
September 3, 2011 - 3:26 pm
Member Since: September 17, 2008
Forum Posts: 667
sp_UserOfflineSmall Offline

A silly question here raihan84, [quote:1hf0u71r]But I want to show the result as 25:00:00[/quote:1hf0u71r]

Is this all you need / want, since an @IF command could clear this problem for you (at least in Lotus), Mindblower!

"For the needy, not the greedy"

Carol Bratt
November 2, 2011 - 11:51 am
Member Since: August 11, 2011
Forum Posts: 67
sp_UserOfflineSmall Offline

Ok, I apologize profusely for my absence and making you wait for a reply. I am truly sorry but due to circumstances beyond my control I and family obligations I was delayed in replying to this query. Hopefully I will make up for it by giving you good advice.

Ok, here we go:

First, if you want to use real time values in your calculations you need to know the "magic" numbers to keep in mind. These are:

60 (Sixty Minutes)
60 (Sixty Seconds)
3600 (60 Secs * 60 Mins)
24 (Twenty Four Hours)
1440 ( 60 Mins * 24 Hours)
86400 (25 Hours * 60 Mins * 60 Secs)

Now that you know these numbers and the information above you should no longer have problems with the manipulation of time and dates in Excel. You must be certain that your times are valid though.

To enter a time that is based on the 12-hour clock, type a space, and then type "a" or "p" after the time; for example, 9:00 p. Otherwise, Excel enters the time as AM. To Enter a time based on a 24-hour clock (military time) enter as 21:00, 11:00 etc.

To enter the current time, press CTRL+SHIFT+; (semicolon).

[b:17cs4ccz]Adding/Subtracting Excel Dates & Times.[/b:17cs4ccz]
As Excel Dates & Times are seen as Serial Values (Dates) and Decimal Fractions (Times) we can easily add or subtract them like below;


=A1+A2 or =SUM(A1:A2) See Also: Adding Excel Times Past 24 Hours

When subtracting dates we naturally want to subtract the lesser date from the greater date. If you are unsure which cell will house which you can use;


[b:17cs4ccz]Subtracting Times That Span Past Midnight Into a New Day[/b:17cs4ccz]

Let's say A1 hasthe Start time of 6:00PM and A2 the end time of 2:00AM. If we need to work out the hours worked between 6:00PM of 1 day and 2:00AM of the next day we cannot simply use;


As Excel does not like negative times

Instead, we should use;


This will then add 1 (1 day) to A2 if the time in A1 is of a higher value than the time in A2. The expression (A1>A2) will equate to either TRUE or FALSE. TRUE has a value of 1 and FALSE a value of zero.

Add/Subtract Days to a Date

To add/subtract, say 2 days to a date, we can simply use;



Calculate the Difference Between 2 Dates

To find out the difference in 2 dates use:


Where A1 houses the earliest date. This will result in the number of days between 2 dates.

If, when working with dates and times we cannot know in advance which date or time is the earliest we can use the MIN and MAX functions, for example we could use:


The syntax for the DATEDIF function is;

DATEDIF(Start _Date,End_Date,Unit)

Valid Units are any one of the formats below

"M", "D", "Y", "YM" (The months and years of both dates are ignored) ,"YD" (The days and years of both dates are ignored) and "MD" (The years of both dates are ignored).

Convert From Decimal Time

If you have the number 5.50 and you really want 5:30 or 5:30AM use:

=A1/24 and format as needed.

If it should be 17:00 or 5:30PM use:


Convert To Decimal Time

To get the opposite, that is a decimal time from a true time, use


Extract Date Only From Date & Time

If a cell has both the true date and true time (e.g. 22/Jan/02 15:36) and we only want the Date, use:


Extract Time Only From Date & Time

To get the time only use:

=MOD(A1,1) and format as needed.

Negative Times

By default, excel cannot calculate or show true negative times, instead you will see ############. This can be overcome by switching Excel to the 1904 Date System via Tools>Options - Calculations and check the 1904 Date System checkbox. The 1904 date system changes the Starting date from which all dates are calculated from January 1, 1900, to January 2, 1904. This is VERY IMPORTANT as, if you are using other Date calculations in the same Workbook they will end up returning erroneous results.

Add/Subtract Months or Years to a Date

We can also easily tell Excel to add to any date any amount of days, months or years. Here is how:


So to add 1 month to a date in cell A1 we could use:


to add 1 year to a date in cell A1 we could use:


There are however some other Date and Time functions Excel has that are part of the Analysis ToolPak Click Add-Ins on the Tools menu. Click to select the Analysis ToolPak check box, and then click Yes if you are asked if you want to install it.

You will then have functions such as:





All of these will be found under the Date & Time category of the Paste Function dialog, Function Wizard.

I hope you find this information useful!

Alan Wade
February 10, 2013 - 1:21 pm
Member Since: January 18, 2013
Forum Posts: 43
sp_UserOfflineSmall Offline

I know this is an old topic but should anyone else want hours in an Excel work sheet to total past 23 then just format the cell as such [hh]:mm:ss;;;@

With that the hours will add up past one day e.g. 25 etc but the minutes and seconds wont.

Forum Timezone: America/Indiana/Indianapolis
Most Users Ever Online: 2303
Currently Online:
Guest(s) 13
Currently Browsing this Page:
1 Guest(s)
Top Posters:
Chad Johnson: 867
Mindblower: 667
carbonterry2: 356
Flying Dutchman: 278
grr: 211
Member Stats:
Guest Posters: 11
Members: 3200
Moderators: 7
Admins: 3
Forum Stats:
Groups: 8
Forums: 20
Topics: 1947
Posts: 13539
Newest Members:
olvet, allenjunker, edgecuff, sarahjohnuk71, joelbelcher
Moderators: Carol Bratt: 67, dandl: 740, Jason Shuffield: 1, Jim Canfield: 8, Terry Hollett: 0, Stuart Berg: 0, John Durso: 0
Administrators: Jim Hillier: 2705, Richard Pedersen: 204, David Hartsock: 1117
Scroll to Top


Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!