*In this tutorial, you will learn the specificities of Excel time format as well as how to add a timestamp using shortcuts or insert an auto updatable time with the NOW function. You will also learn how to apply special Excel time functions to get hours, minutes or seconds from a time stamp. *

Microsoft Excel has a number of helpful time features and knowing them a little in depth can save you a lot of time. Using special functions, you can insert the current date and time anywhere in a worksheet, convert time to a decimal number, sum different time units or calculate the elapsed time.

To be able to leverage powerful Excel time functions, it helps to know how Microsoft Excel stores times. So, before digging deeper into the formulas, let's invest a couple of minutes in learning the basics of the Excel time format.

If you have been following our Excel Dates tutorial, you know that Microsoft Excel stores dates as sequential numbers beginning with January 1, 1900, which is stored as number 1. Because Excel treats time as a portion of a day, times are stored as decimal fractions.

In Excel's internal system:

- 00:00:00 is stored as 0.0
- 23:59:59 is stored as 0.99999
- 06:00 AM is 0.25
- 12:00 PM is 0.5

When both a date and time are entered in a cell, they are stored as a decimal number comprised of an integer representing the date and a decimal portion representing the time. For example, *1 June 2015 9:30:00 AM* is stored as 42156.3958333333.

A quick way to pick a decimal number representing a certain time is to use the **Format Cells** dialog.

Simply select a cell containing the time and press Ctrl + 1 to open the *Format Cells* dialog box. On the *Number* tab, select *General* under *Category*, and you will see the decimal in the *Sample* box.

Now, you can write down that number and click *Cancel* to close the window. Or, you can click the *OK* button and have the time replaced with a corresponding decimal number in the cell. In fact, you can think of it as the fastest, easiest and formula-free way to **convert time to decimal** in Excel. In the next part of our Excel Time tutorial, we'll take a closer look at special time functions and calculations to convert time to hours, minutes or seconds.

Microsoft Excel is smart enough to recognize a time as you type and format the cell accordingly. For example, if you type *20:30*, or *8:30* *PM*, or even *8:30 p* in a cell, Excel will interpret this as a time and display either *20:30* or *8:30* *PM*, depending on your default time format.

If you want to format some numbers as times or apply a different time format to existing time values, you can do this by using the **Format Cells** dialog, as demonstrated below.

- In an Excel sheet, select the cell(s) where you want to apply or change the time format.
- Open the
*Format Cells*dialog either by pressing Ctrl + 1 or by clicking the*Dialog Box Launcher*icon next to*Number*in the*Number*group, on the*Home*tab.

- On the
*Number*tab, select**Time**from the*Category list*, and choose the desired time format from the*Type*list. - Click OK to apply the selected time format and close the dialog box.

Though Microsoft Excel provides a handful of different time formats, you may want to create your own one that fits the best for a particular sheet. To do this, open the *Format Cells* dialog box, select **Custom** from the *Category list* and type the time format you want to apply in the *Type* box.

The custom time format you've created will be in the **Type** list the next time you need it.

When creating a custom time format in Excel, you can use the following codes.

Code |
Description |
Displays as |

h | Hours without a leading zero | 0-23 |

hh | Hours with a leading zero | 00-23 |

m | Minutes without a leading zero | 0-59 |

mm | Minutes with a leading zero | 00-59 |

s | Seconds without a leading zero | 0-59 |

ss | Seconds with a leading zero | 00-59 |

AM/PM | Periods of the day (if omitted, 24-hour time format is used) |
AM or PM |

The following table provides a few example of how your Excel time formats may look like:

Format |
Displays as |

h:mm:ss AM/PM | 1:30:00 PM |

h:mm | 13:30 |

dddd, m/d/yy h:mm AM/PM | Tuesday, 1/13/15 1:30 PM |

ddd, mmmm dd, yyyy hh:mm:ss | Tue, January 13, 2015 13:30:00 |

When you are calculating times in Excel, a cell containing the sum of time amounts may exceed 24 hours. To get Microsoft Excel to display times beyond 24 hours correctly, apply one of the following custom time formats.

Format |
Displays as |
Explanation |

[h]:mm | 41:30 | 41 hours and 30 minutes |

[h]:mm:ss | 41:30:10 | 41 hours, 30 minutes and 10 seconds |

[h] "hours", mm "minutes", ss "seconds" | 40 hours, 30 minutes, 10 seconds | |

d h:mm:ss | 1 17:30:10 | 1 day, 17 hours, 30 minutes and 10 seconds |

d "day" h:mm:ss | 1 day 17:30:10 | |

d "day," h "hours," m "minutes and" s "seconds" | 1 day, 17 hours, 30 minutes and 10 seconds |

The custom time formats discussed above work for positive values only. If the result of your calculations is a negative number formatted as time (e.g. when you subtract a bigger time amount from a smaller one), the result will be displayed as #####. If you want to format negative time values differently, the following options are available to you:

- Display an
**empty cell**for negative times. Type a semicolon at the end of the time format, for example**[h]:mm;** - Display an
**error message**. Type a semicolon at the end of the time format, and then type a message in quotation marks, e.g.*[h]:mm;"Negative time"*

Generally speaking, a semicolon acts as a delimiter to separate positive values' format from negative values' formatting.

If you want to display negative times as **negative values**, e.g. -10:30, the easiest way is to change the Excel's Date System to 1904 date system. To do this, click *File > Options > Advanced*, scroll down to *When calculating this workbook* section and check **Use 1904 date system** box.

When changing the time format in the *Format Cells* dialog, you may have noticed that one of the formats begins with an asterisk (*). This is the **default time format** in your Excel.

To quickly **apply** the default Excel time format to the selected cell or a range of cells, click the drop-down arrow in the *Number* group, on the *Home* tab, and select **Time**.

To **change** the default time format, go to the *Control Panel* and click **Region and Language**. If in your Control panel opens in *Category* view, click *Clock, Language, and Region* > *Region and Language* > *Change the date, time, or number format*.

There are a numbers of ways to insert time in Excel, which one to use depends on whether you want a static timestamp or a dynamic value that updates automatically to reflect the current time.

If you are looking for a way to insert a timestamp in an Excel cell, i.e. a static value that won't automatically update whenever a workbook is recalculated, then use one of the following shortcuts:

- To insert the
**current time**, press Ctrl + Shift + ; - To enter the
**current date and time**, press Ctrl + ; which inserts a date, then press the Space key, and then hit Ctrl + Shift + ; to insert the current time.

If you aim to insert the current date and time as a dynamic value that refreshes automatically, then use the Excel NOW function.

The formula is as simple as it can possibly be, no arguments are required:

`=NOW()`

When using the NOW function in Excel, there are a few things to keep in mind:

- The NOW function retrieves time from your computer's system clock.
- NOW is one of Excel's volatile functions that cause the cell with the formula to recalculate every time the worksheet is re-opened or recalculated.
- To force the Excel NOW function to update the returned value, press either Shift + F9 to recalculate the active worksheet or F9 to recalculate all open workbooks.
- To get the NOW function to automatically update at a specified time interval, add a VBA macro to your workbook, a few examples are available here.

If you'd rather insert only the current time in a cell without a date, you have the following choices:

- Use
`=NOW()`

formula, and then apply the time format to a cell(s).Please remember, this will only change the display format, the actual value stored in a cell will still be a decimal number consisting of an integer representing the date and a fractional part representing the time.

- Use the following formula:
`=NOW() - INT(NOW())`

The INT function is used to round the decimal number returned by NOW() down to the nearest integer. And then, you subtract the integer part representing today's date to output only the fractional part that represents the current time.

Since the formula returns a decimal, you will need to apply the time format to the cell to make the value display as time.

The following screenshot demonstrates both formulas in action. Pay attention that although the formatted time values look the same (column C), the actual values stored in cells (column D) are different - D4 contains only the fractional part:

The TIME function in Excel is used to convert a text string showing a time into a decimal that represents the time.

The syntax of the Excel TIME function is very straightforward:

`=TIME(hour, minute, second)`

The hour, minute and second arguments can be supplied as numbers from 0 to 32767.

- If
**hour**is greater than 23, it is divided by 24 and the remainder is taken as the hour value.For example, TIME(30, 0, 0) equates to TIME(6,0,0), which is 0.25 or 6:00 AM.

- If
**minute**is greater than 59, it is converted to hours and minutes. And if**second**is greater than 59, it is converted to hours, minutes, and seconds.For example, TIME(0, 930, 0) is converted to TIME(15, 30, 0), which is 0.645833333 or 15:30.

The Excel TIME function is useful when it comes to merging individual values into a single time value, for example values in other cells or returned by other Excel functions.

To extract time units from a time stamp, you can use the following Excel time functions:

**HOUR(serial_number)** - returns an hour of a time value, as an integer from 0 (12:00 am) to 23 (11:00 pm).

**MINUTE(serial_number)** - gets the minutes of a time value, as integers from 0 to 59.

**SECOND(serial_number**) - returns the seconds of a time value, as integers from 0 to 59.

In all three functions, you can input times as text strings enclosed in double quotes (for example, "6:00 AM"), as decimal numbers (e.g. 0.25 that represents 6:00 AM), or as results of other functions. A few formula examples follow below.

`=HOUR(A2)`

- returns the hours of the timestamp in cell A1.`=MINUTE(A2)`

- returns the minutes of the timestamp in cell A1.`=SECOND(A2)`

- returns the seconds of the timestamp in cell A1.`=HOUR(NOW())`

- returns the current hour.

Now that you've got down to brass tacks of the Excel time format and time functions, it will be much easier for you to manipulate dates and times in your worksheets. In the next part of our Excel Time tutorial, we are going to discuss different ways of converting time to numbers. I thank you for reading and hope to see you on our blog next week!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 159 responses to "Excel time format & how to use NOW and TIME functions to insert time"

please time 48 hours out put

48 hours example

Hi Anant,

Sorry, I am not sure I can follow you. Could you elaborate a bit?

I want the negetive time to show as ZERO when subtracting the time..

23:40 - 23:30 = ##### instead of that i want it to be 00:00

Hi Ali,

You can use a formula similar to this:

=IF(B2-A2<=0, "00:00", B2-A2)

Hi madam,

HI Madam

Please help me actually i want to use a formula for my office which is very important for me. I want to calculate time like 02.30 to 3.10= 00.40, +13.30 to 14.00= =00.30 thier sum as 01.10 hrs in ecxel but i m unable to do the same.

plz provide ur mail so i can send u excel sheet.

Plz help

regards

Anil chopra +91-9992318962

Hi Svetlana, is there any wy I could insert the time greater than 24h (80 hours for example) using TIME(hh;mm;ss) function? Or is there any solution to compare the time... Ig the time is greather or equal to 80h?

Hi,

My query is,I will have to maintain In time and out time register for our staff to monitor overtime of work for which "I PREPARE FOUR COLUMNS, NAME, FATHER NAME DESIGNATION, GENDER IN TIME AND OUT TIME COLUMN, When I enter in time value, I need the out time value should be fill by counting 9 Hrs. from the time of In time Entry.

Pleas advice me Sir.

Regards,

JRK Prasad

hello,

could you please help me ?

I have cell contain date like (03/01/2015)

and I have cell contain time (11:34:01)

how can I put them in one cell to become (03/01/2015 11:34:01)

could you please give me the format

thanks

ahmed

from Iraq/ basra

petroleum engineer

Hello Ahmed,

You may use CONCATENATE functions to combine those two cells.

hope this will help

CTRL+; for date

CTRL+SHIFT+; FOR TIME

but this shortcut wont show seconds. How can i use this shortcut and it will include seconds as well...thanks

just use =NOW() and format.

=TEXT(A1,"dd/mm/yyyy")&" "&TEXT(A2,"HH:MM:SS")

A1 = Date

A2 = Time

type '03/01/2015 on cell A1(or on any cell) and '11:34:01 A2(or on any cell).

Then on any cell type =concatenate(a1," ",a2)

typing apostrophe (') sign before any numerical value or any special character displays it as it is.

Hi,

I want to calculate over time of employees. Would you like to help me in this regards as 10:30PM-10:00AM= ?

Regards

EXAMPLE= STARTING TIME(A), FINISHED TIME(B), TOTAL HOURS(C)

FORMULA IS

C=SUM(12+B)-A

A B C D E

Working Starting Time Working Ending Time Actual Work hours Worked hours Over time

10:30 AM 10:00 PM 9:00:00 11:30:00 2:30:00

=C1-B1 =E1-D1

All Cell Format should be : Format cells (Cntrl + Shift + F) > Number Tab > Time > Selecet "HH:MM:SS" I can be 7th one in list

My time format is set to HH:MM:SS in excel but, it shows as H:MM:SS in spreadsheet, ie: 1:15.00; I want it as 01:15:00. Please help.

Whilst certainly not commonly used... I'm trying to keep my spreadsheets presentable and not confusing.

Cell Formats for Time:

Within the accounting function/format; if the cell = 0.00, -

How do I get excel to do the same with time; if the cell = ([h]:mm) 0:00, - or "blank"

You help is appreciated

Hi All,

If you've seen this and started to play with it, thanks for your attempt... I have found my solution by playing:

[h]:mm;;_-* "-"??_-;_-@_-

Kindest Regards

Hi,

I can't get this formula to work: =TEXT(TIME(HOUR(A1);MINUTE(A1);0);"hh:mm")

Regardless of how I write the formula or format the cell, the result shows 'hh:01' and not the time given in the source cell, which itself contains a formula. The source cell lists both date and time, and I'm trying to extract the time. The file is an .xls-file, not sure whether that has an impact. I've run out of ideas of how to solve it, does anyone have a good solution?

Kind regards

hi,

please tell me

16/11/2016 10:03:40 AM

-16/11/2016 11:08:08 AM

show me hours ..

Just type NOW()-INT(NOW())in Cell A1

Then extract hours, mintues & seconds details from Cell A1

A2=HOUR(A1)

A3=MINUTE(A1)

A4=SECOND(A1)

I hope, it would serve as a solution to your issue & I'm not able to attach screenshot of the whole demonstration

Hi,

How do i convert 53212 mins into hh:mm:ss format i used the following to get hh:mm but im not getting SS please guide me in this behalf

Tpe1 =TEXT(FLOOR(A1/60,1),"00")&":"&TEXT(MOD(A1,60),"00")

Type2 =INT(A1/60)&":"&TEXT(MOD(A1,60),"00")

Hi,

how to use Sum if for time units sum ??

i have the list of time units for a month for more staffs ! how to get sum of Time units for staffs ! (Using Sum if)

I have cells that time within for a schedule.

ex: 0800-0830

next cell underneath 0830-0900.

I want t update the time in the 1st cell, is there a way for the time in the cell underneath to automatically update or to I have to go into each cell to update the times? thank you

I am trying to calculating elapsed time in hours but I need to account for end times that are blank and to count those times as current time or newtwork hours.

I want to find hours left on a project ,so I have hours worked and hours billed =hours left ,how do i create a formular

KAD6336 1 01/06/2024 13:49

RVS2196 1 01/06/2025 20:39

LAG3131 1 01/07/2015 21:59

ADA2259 1 01/07/2016 03:28

LAG0060 1 01/07/2016 05:15

ADA2312 1 01/07/2016 09:06

UW3274 1 01/07/2016 09:24

LAG0127 1 01/07/2016 11:10.

Second post

KAD6336 1 06/24 13:49

RVS2196 1 06/25 20:39

LAG3131 1 07/15 21:59

ADA2259 1 07/16 03:28

LAG0060 1 07/16 05:15

ADA2312 1 07/16 09:06

UW3274 1 07/16 09:24

LAG0127 1 07/16 11:10

LAG3216 1 07/16 13:44

LAG3114 2 07/16 13:18

Second post is what i got when i copied from whatsapp and pasted on world while the first is what i got when i copied and pasted on excel. please how can i get the time format to appear with the same format when i copy from whatsapp. Note:this occurs when whatsapp message is sent to me from another organisation. i dont have such issue when it is sent from my organisation. how can i get the format of the first post as it is in the original message. thanks

How to convert DD:h:mm in to H:mm formate For Ex. 02(Day):01(Hours):10(Min.) to 49(Hours):10(Min) in excel.

Hello Sir,

I need to represent data to show the jobs completion time on a given week.

On 30th july, the job completed the next day ie 31st july at 12:43 am.

since my x-asis has time frame of 0- 24 hours, the graph is not picking the next day completion time.

Pl can you help me out to get the graph even though the job completed the next day

thx

Hi,

Just wondering how to get the correct duration (time elapsed), for example, between 23:00 and 00:05. I used simple subtraction but it returns negative result.

thank you

Hi Steven,

I have just tried this:

A1 is 23:00

B1 is 00:05

The formula =A1-B1 returns 22:55

Do you get a different result in your Excel?

In time 21.50 ( night 11.50) and closing time 7.00 am but time not calculate can u help me.

In time 21.50 ( night 11.50) and closing time 7.00 am but time not calculate can u help me.

no need negative value

I have some time based trading data in seconds, but some of the seconds are excluded from the table since no trading happened then. How can I insert the missing seconds?

I want set the all the values till end of the record to the previous day 11:59 pm how to I do that

Hello-

How can i have the time enter automatically when someone selects or clicks a name from a drop down list in a different column. Is that even possible?As opposed to entering time manually.For exmample lets say column B has a drop down list of people and i want the time to auto populate in column q with the start time when someone clicks a name in that cell(B2). Each person would have to manually or short cut the end time. Time can vary between people.

B3 Q3

Moe 10:30 PM

Larry 10:45 PM

Curly 10:57 PM

Hello cez,

Thank you for the detailed description.

Please see the only way that we know how to do this without using a macro in this article:

https://www.ablebits.com/office-addins-blog/2015/03/18/insert-dates-excel/#insert-timestamp

Instead of NOW() please use NOW()-today()

Hi - how do I set a conditional formatting that accounts for a potential date change due to the 24hr time? For example, if a therapist visits a patient on X date but at the end of the day 4pm, the date will change to the following day. Can you assist with the formula for this?

I currently am using this formula: =IF($J$25="","",($J$25+139))+1 for date

&

this formula for time: =IF($W$25="","",($W$25-TIME(0,15,0)))

Thanks!

i have to print a value in another column.The condition is if the time exceeds 12:00:00,add 1. Time is mentioned in H column and the data in which addition is to be done is in column G. The value to be returned is in column J.

I am using formula "=if(G2>"12:00:00",sum(G2,1),G2)".This formula doesn't implement in some cases. How to do it?

PS: Time is in 23:59:59 format.

Hi Svetlana,

Is there a way to format the cell to show GMT-0500 at the end of a date/time cell to indicate the time zone?

Example: 2016-10-17 03:10:42 -0500 or 2016-10-17 03:10:42 GMT-0500

Thanks,

G

How can i check if there is an update in excel file

how could i use =NOW() , which shows time but it will not change/update automatically. when i'll use this then it will only shows insert time only. please help for it.

I'm trying to calculate time difference (start time - end time = total time) but also enter start/end times without having to type the colon. If I format the time so I don't type the colon my formulas won't work.

I'm using this formula: =TEXT(+H2-E2,"00\:00")+(E2>H2) which works .... but ONLY if the seconds in my end time are larger than start. Otherwise, it calculates based on 100 versus 60 (for time).

The time format I'm using so I don't have to type the colon is: 00\:00

Is there a way to quickly enter times (w/o the colon) but have it calculate based on 60 minute increment.

Hi,

I am using the form data,and there are n number of rows, since i want to count the current date(today) responses. i have used the formula as below but it is not reflecting the result.

=COUNTIFS('CCR-2017'!$C$3:$C,"="&TODAY(),'CCR-2017'!$E$3:$E,"GUNA")

if the C:C column is with only date (Without time stampl) it will reflect the result. due the time stamp my formula not calculating the current date.

Can you pls help me on this.

Regards,

Mahesh

Hi,

Pls suggest me how to count the current date, the date is with timestamp as well

I would like to track my improvement over time as I run. I would like to design an Excel spreadsheet that I can glance quickly at and see the number of days I have achieved, or nearly achieved, this goal by the color of the cell. Is it possible to design a spreadsheet which works like this?

All of this would apply to each cell in a column thirty-one rows high:

If time Entered is greater than or equal to 14:52:00, then shade cell red

If time entered is between 13:52:00 and 14:51:00, then shade cell yellow

If time entered is between 12:00:00 and 13:51:00, then shade cell green

One of the major problems I have encountered doing this is that excel converts my minute:second input into time (for example, when I type 15:30, it corrects it to 3:30 PM). I really appreciate your help with this. I am fairly new to Excel and have learned a lot using the information on your site. John

Hello, here is my example.

A plane departs at 10/01/2017 01:00:00 and passengers come to gate 02:00hrs prior, which is the previous day 23:00:00.

How to subtract those 2 hrs from cell containing 10/01/2017 01:00:00? Many thanks!

Hello,

I have taken static time format and found the time difference by =INT(F25-H25)&" Days "&HOUR(MOD(F25-H25,1))&" Hour "&MINUTE(MOD(F25-H25,1))&" Minutes" this formula.

Now, I want to include only the working minutes and exclude the non working days( saturday,sunday and festivals etc) and non working hours (after 6.30pm to 9.30am). It will be very helpful to me if you could tell me the solution asap

Hi

I looking for the calculation of date and time like

01/03/16 4.15 PM to 01/03/17 5.15 PM

Need Answer like this ways....

Total Years Total Months Total Days Total Hours Total Min Total Sec

Hi.... please help me in using time (0,0,0) for more than 24 hours format. I mean the railway time.urgent

Hello Vasanth,

To show a time interval of more than 24 hours, 60 minutes, or 60 seconds, set up a custom time format where a corresponding time unit is enclosed in square brackets, like [h], [m], or [s]. For more details, please see:

How to show times over 24 hours, 60 minutes, 60 seconds in Excel

Hello!

I have formatted Cell to CUSTOM dd"d"hh:mm and getting results 04d04:04

for 04 Days 04 HRS 04 MIN. 3.16997 Days which is exactly what i want.

But considering the same formatting i am getting result 01d04:04

for 00 Days 04 HRS 04 MIN instead of 00d04:04.

Hope you understand my query, and please help me to get result 00d04:04 as per above explanation.

Aizaz

Hi,

i am having a problem with the date.

Issue : i have a excel sheet i have exported it from a software. When is share this file from personal machine to my work machine the date changes from \ to - but when i send it to my client it works fine from same machine.

i have changes the format locale to English(United States), now i am able to get the date as 14\02\2017 but the seconds in time stamp is missing in few cells

for E.g date(in my machine)-14\02\2017 02:10:55

in work machine -14\02\2017 02:10

But in few cells the date is displayed as expected but not in few.

Moreover, when i select the cell where ss is missing i am able to see date in formula bar as 14\02\2017 02:10:55 PM

Will you be able to help me ? it's bit urgent

Hello, Bharat,

this may happen if the date and time are formatted differently on your computers. Please, take a closer look at this part of the article to make sure that you apply one and the same time format on your machines.

Hi

Can I extract hours from format HH:MM having more than 24 hours. For example, I summed-up overtime for month = 56:34, How can I extract "56" using hour function?

Regards,

Saad Kapadia

I have two cells each with the MS Date and Time Picker Control 6.0 (SP4) in a different cell I want to return the difference in # of business days between the two selected dates.

thanks in advance!

Hi what excel formula would I need to enter to work out the How much overtime has beern worked between 09:00-12:00? From the example data

A. B

Start. End

10:00 18:00

18:00. 22:00

How i can get a current time with the seconds in the excel and also need to get current time in the below cells.

I hope someone can help. I have extracted time from an application. When I put it in excel it reads it as a text, that makes sense. Now, I am trying to convert back to time...it reads 126 as 1:26 - the time is actually 00:01:26...I have tried every trick I can think of. Can anybody help me figure this out?

I have added daily overtime for the month in format hh:mm. Now I want to display separately Hours and Minutes.

I can extract minutes from formula =Minutes(cell) but for hours greater than 24, it gives me wrong answer. for example, if the hours are 34, formula will minus 24 and give answer 10.

Kindly advice.

Hi How to calculate 108 hrs post from 11/07/2017, 6:00, kindly share the formula.

Regards,

Sowmyashree

Hi Sowmya,

Thank you for your question.

Please first select the cell with your data, click Format Cells -> More Number Formats -> Date and set format 7-11-17 6:00 AM. Then try the following formula:

=A1+1/24*108

Hope it helps.

hi, i need help in excel time sheet.

Example data available

A = 15:55

B = 20:25

C = 30:30

Total = 66:50 (by time formula)

I need result in number

A = 15.55

B = 20.25

C = 30.30

Total = 66.10 (by number formula)

Please help us

I simply want to display AM or PM in a cell based on the current computer time, no time or date.

I have tried =IF(A2<0.5,"AM","PM") it will show AM but doesn't change for me when the computer time changes and I refresh the spreadsheet.

I've also tried the =NOW() and a custom time format of AM/PM with no time.

This seems to be a simple problem but the correct solution has eluded me.

Any one have suggestions?

Use [$-F400]h:mm:ss AM/PM

But you have to be on a 12 hr clock not 24 hr

Hi, I have in a formatted the cell to display a date and time (27/01/2017 15:04). I would like a formula to calculate/conditional format to work out if the time is between 07:00 and 00:00

Hello All,

I exported a data from the timekeeping software which exported in the following format as 07/10/2017 06:58, but we need to see the output coming as 7/10/2017 6:58:00 AM. The problem is

1- The format function is not working on the cell as its an exported data for some reason it doesnot apply to this.

The format does change to 7/10/2017 6:58:00 AM automatically on ONLY WHEN I click inside the cell and hit ENTER, but then we have to do this for thousands of cells one by one.

I forgot the formula to convert this to decimal, if someone can help me with that would be a great help

How can get a formula result of time blocks in my column to display as: 9.30am-10.00am

10.00am-10.30am

10.30am-11.am

145:59:38 i have a question how do I covert it to seconds.

Hi,

I have a column with Date and Time. I need to write a formula to show, if the date and time is 10-10-2017 12:53 PM, if time is after 2:30 PM the date to change to next date. Please advise.

Hi, Austin,

could you please specify where is that "next date" located? Should the formula just return the next date from the list or do you have an additional list?

Hi - I'm trying to evaluate a planned timetable against the actual journey times. Generally straightforward (a simple a-b gives the right answer) but if my journey time took less time than scheduled (ie I'm planned to arrive at 10:15 but arrived at 10:05) how can I automatically calculate this to show a response of -00:10?

Your guidance as ever...

Hello, Tablespider,

Please try the following formula:

=IF(A1>=B1,A1-B1,"-"&HOUR(B1-A1)&IF(MINUTE(B1-A1)<10,":"&"0"&MINUTE(B1-A1),":"&MINUTE(B1-A1))&IF(SECOND(B1-A1)<10,IF(SECOND(B1-A1)=0,"",":"&"0"&SECOND(B1-A1)),":"&SECOND(B1-A1)))

Hope it will help you.

Hi,I have a coloum with time format - 1 h 10 m 16 s

How can i convert to show 24:00:00.

plz guid

Hi guys. I need help please...

I need to calculate the number of lates and undertime using this details

B2 - date of time in/out

C2 - time in/out

E2 - work schedule in date

F2 - time schedule

Thanks guys hope for you immediate response.

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

DOWN_DATE DOWN_TIME CLEARED_DATE CLEARED_TIME

15.12.2017 5:27:04 15.12.2017 8:18:38

14.12.2017 18:34:24 14.12.2017 20:56:45

30.12.2017 12:17:42 30.12.2017 13:28:11

13.12.2017 7:11:34 14.12.2017 8:53:06

15.12.2017 5:27:14 15.12.2017 8:18:12

how to calculate diffrence of down duration .in excel 13.12.2017 7:11:34 -14.12.2017 8:53:06 showing as 1/1/1900 1:41:32 AM .which is not correct .tell me correct formula pl

Hello,

If I understand your task correctly, please try the following formula:

=ROUNDDOWN(B1-A1,0) & " days " & TEXT(TIME(HOUR(B1-A1),MINUTE(B1-A1),SECOND(B1-A1)), "hh:mm:ss")

Where cell A1 is â€ś13.12.2017 7:11:34â€ť, cell B1 is â€ś14.12.2017 8:53:06â€ť

Hope this will help you!

how can you get the time elapsed in h:mm from to date & times. example( 12/30/2017 18:16 / 1/2/2018 4:39. What is the time elapsed between these two?

How do I add to this formula to start calculating time after a 24hr allotment.... I want to start calculating after the first 24hrs - HELP!!

=IF(INT(B2-A2)>0, INT(B2-A2) & " days, ","") & IF(HOUR(B2-A2)>0, HOUR(B2-A2) & " hours, ","") & IF(MINUTE(B2-A2)>0, MINUTE(B2-A2) & " minutes and ","") & IF(SECOND(B2-A2)>0, SECOND(B2-A2) & " seconds","")

Do I need formul to subtract time, like 88657:53 - 745563:32

Or do I have to change time in to numbers?

7:46 7:46:00 AM

1:05:32 1:05:32 AM

CELL SHOWING 51:45:00 BUT INSIDE CELL 02-01-1900 3:45:00 AM

THIS IS EXPORT SITUATION FROM WEBSITE REPORT ACTUALLY THIS IS MINUTES AND SECONDS 51:45

I am trying to edit a basic time card in Excel made by others. I put the time in: ie: Cell G:25, I want to write: 0800, and it fill in as 8:00, then Cell G:26 write 1300, and it fill 1:00, Cell G:27 write 1330, fill 1:30, Cell G:28 1630, fill 4:30. Then Cell G:29 totals how many hours I worked that day. 8.00. And finally in Cell M:29 a total of how many hours were worked that day.

I apologize if I don't make sense, but any help would be amazing!

Thank you.

Hi I am having a doubt in excel, I am having a table with 4 columns in which two fields are date field, If i enter a value in first column the current date and time should be entered in the second column(date column), is there any way to be done for this.'Thanks in Advance

Ram:

Where the first cell, first column is A1, enter this in the first cell, second column:

=IF(A1"",NOW(),"Empty")

It says: If A1 is not empty enter current date, otherwise enter "Empty".

Format the first cell, second column as DATE with the time included.

You can enter any text you deem appropriate in place of "Empty".

Please guide me to get the difference between two time stamps in the following format

20-09-2018 13:00:43 20-09-2018 15:30:31

hi i have 79 hrs 30 mins which is written as 1/3/1900 7:00:00 AM on the dialog box ....now i want to convert it into 79.30...

how can i do this?? please help urgent

Hi

can you help me in speed data entry getting info by phone

0730

0835

0955

1045

1515

if my data entry like above i need this to convert to time or else need to put ":" in mid (I used left and right formula but i want the same cell value directly change to time format)

is there any formatting or other option to support my requirement please.

Thanks in advance

can anyone say me how i can convert 0:00 to 00:00 until to 9.

I'm trying to convert the cells with a text format 5 days, 6 hours, 0 minutes and 0 seconds to

custom format like d hh:mm:ss in order to complete an in cell calculation. How can this be done ?

Hi, I am trying to calculate how much time it took someone to close a ticket. The report that I get out of Service pro shows the dates like this:

Ticket # Time Logged Time Closed

12/28/2018 1:50 PM 1/7/2019 8:23 AM

I need to calculate the difference between the 2 time periods so that I can then average this amongst all of the tickets that were closed. Can anyone help me?

Let's say 12/28/2018 is in A1 and 1/7/2019 is in A2 and the number of tickets (let's it is 400) is in A3. =((A2-A1)*24+INT((((A2-A1)*24)-INT((A2-A1)*24))*60))/A3 will give you 0.668875, which is the number of minutes per ticket. Multiplying that number by 60 gives you 40.1325 seconds per ticket, on average.

How do I add to this formula to start calculating time-sheets Where the first cell,first column is A1 (in time),second column is B1(out time) third column is C1(working hours), enter this in the first cell, fourth column:

=IF(C1>9,9,C1)

Nine is should be time format

I need fourth column (normal working hours it should blow the nine) and fifth column(it is OT hours C1-D1)

Noted all are Time format please help me....

I want to determine if the current time is between two preset times. Let's say I want to know if the current time is between 10:00 (in A1) and 11:00 (in A2). If it is currently 10:30 and I enter "=now()>A1", I get a return of "TRUE". But if I then enter "=now()<A2", I get "FALSE". Why is that?

Hi!!

Im trying to use the =today()+1 formula, i need it to updated the date when i open the file but i need it to stay the same date (not update) at midnight

I am doing a Work Project where we need to calculate minutes used for activity, we use "=(P9-O9)*1440" but when going 23:00-00:10 instead of 70 it gives us -1370 Any tips?

Incase anyone comes across this same problem, you just have to add the Date before the time to solve it.

Please can you tell me the easiest way to insert a static timestamp including seconds in a cell. If I use 'Ctrl + Shift + ;', I get say 23:35, but I need the seconds to appear too, e.g. 23:35:33. Please can you help? Thank you.

I need to split date and time in two different cells from the below format

2019-06-21 18.36.30

Have a column to check if timesheet add-up correct. Am getting a ##### error sometimes and othertimes get what 0:00 result when using formula: =IF(G21="","",G21-SUM(I21:P21))

G21 is the sum of start, end, & breaks times. Columns I-P are the division of total work hours (G21). The problematic QC formula is the check if G21 and the sum of projects are equal & equal 0:00 hours. Note: All cells are in custom format: [h]:mm;@

EXAMPLE:

cell G21 formula: =IF(D20="","",($E20-$D20)-F20)

cell G21 result: 8:30

cells I21-P21 values: 0:30/1:00/1:30/0:30/1:00/1:30/2:00/0:30

cell I21-P21 result: 8:30

cell Q21 checking formula: =IF(G21="","",G21-SUM(I21:P21))

Sometimes given Q21 result of 0:00 and other times #######

Hi i would like to extract a data of the last 24 hours in my data base. which means i have to use intervals. i would like to use Now function to return me the last 24 hours data from now.

these values will be updating hourly as the data base keeps changing.

Ex. when i want to extract data for the last 30 days i use today function for the time interval.

Start: today-32

end: today

this gives me 32 data and will always change the last day as time goes.

i would like to use similar method for hourly data extraction using NOW Function.

Start:

End: NOW

HOW CAN I GET THE STARTING TIME USING NOW'S FUNCTION AS I EXPLAINED WITH TODAY'S FUNCTION

Hi team, I want to enter exact time, using Ctrl+Shift+; but it omits seconds, only shows hours and minutes (in hh:mm:ss format, i.e., for example: 11:34:00 against the timing of 11:34:45) please help.

hi guys,

i need to know in my scenario..... how to show the result when its get mature

like... i have some checks due in next month which i want that when the date come, it automatically appear in my another maintained sheet.although, the date and all details with despcrition explained in first sheet but in other sheet i need their appearance

so i need to know how these details appear at the time of maturity

appreciate your response back

IF 17:30PM-9:00AM=8:30 HOURS OR 16:30PM-9:30AM=7HOURS THAN HOW TO I KNOW 31 DAYS CALCULATION IN EXCEL

Hi Svetlana,

Good day!

Could you please let me know how to convert 1/2/1900 2:57:10 AM data format to HH:MM:SS. I have an excel full of different types of values (12:15:34 AM, 1/2/1900 2:57:10 AM). Any help would be appreciated. Thanks

hi madam,

I have one doubt , i am key in excel my workers overtime day shift time in 6.00 out 19.30 i use formula =((19.30-06.00)-9) 4.30 , 9 meaning company hours the rest overtime this is good result , but night shift how to use formula, in 18.00 out 08.00 , please madam can you explain me formula.

How can I convert days, hours, minutes, seconds to time format Hh:mm:ss with a formula? The data in excel was extracted from a database and not formatted as an actual time format.

Example: Convert 4 days, 22 hours, 36 minutes, 58 seconds to 118:36:58

I need a small help that I am unable to convert hours into seconds as I have been trying 7:00/ 1440 for this I am getting a perfect answer but as coming to 6:30/1440 the calculator answer and this answer is not matching can you help me with this ?

i am trying to create a timesheet that will limit the amount of "time worked" in a cell to 12 hours, because everything over 12 hours is considered overtime i was fairly certain my formula would be =IF ((C1-B1, "h:mm:ss") *24) > 12 , 12 ,(C382-B382, "h:mm:ss") but i keep getting a #ERROR, i have tried multiple different variations and cant seem to get this to work. any suggestions?

Hi,

How can I just display time instead of date & time in the cell. As I need 22:00 but the cell will show 14/05/1903 10:00:00pm.

Hi,

I've a doubt, i want to compare two columns in Time format and highlight the one that exceeded. I can only able to compare it, but I would like to know how to highlight it?

For example:

Shift time Login Time Status

2:30 2:25 Met

2:30 2:29 Met

2:30 2:45 NotMet

2:30 2:15 Met

2:30 3:15 NotMet

2:30 2:50 NotMet

I can compare whether the condition is met or not, but how to highlight the cells in 2nd column automatically.

I am trying to input hours in one column for example input and subtract

in one column i have 24:13 minutes to be deducted from 113:45 minutes when i input 24:13 it changes to 0:13 also the current formula i use is =(C3-B4) where C3 is 113:45 and B4 is 24:13

Thanks

Hi, I want to record only time of a data entry is it possible to do so?

Hello,

Is there any format that will make my time inputs in PM only. SO if I type in 5:00 it will automatically pick it up as PM and not AM.

I have custom formatted cells with 00/:00 enable simple entry just directly key in number for instant 830 will display 8:30am,but when come to calculating the duration it display hh:mm, may I know how can I get it converted to single minute unit please.

Hello!

Any one please help!

i need to assign formula, if employee works after 9:00pm i need give one food allowance.

Hello Bharath!

Supposing your end time is in B2, the formula below will be the one for you:

=IF(B2 < TIME(21,0,0), B2 - TIME(21,0,0) + 1,B2 - TIME(21,0,0))

I would like to record 24h00 in Excel 2013

Hello Seleko!

Please go to Format Cells, choose Number -> Custom Format and set

[hh]:mm:ss;@

I hope this will help, otherwise please do not hesitate to contact me anytime.

I want to add up hours for an employee and then multiply those hours by her rate to get total cost spent. How can I do this if the hour is in this format hh:mm:ss? The formula needs a whole number like 1.75 rather than 1:45.

Hello!

To convert time to number of hours, and minutes to decimal parts of an hour, use the formula

=HOUR(D1)+MINUTE(D1)/60

Hope this is what you need.

Can Excel actually convert the time from 1:45 to 1.75 through a formula?

Can Excel actually figure out the time of 1:75 is actually 135 minutes?

Hello!

How do you want to write this time value in Excel?

Hi how could i calculate the total time lapse i tried =sum(f2:f4) but its not working

Hi how could i calculate the Night duty hrs which is inside the total hours & btn 22 to 06

Id of Crew|Name |In |Out |D.Hrs|Night D.Hrs

N10001 |AAA |05-06-2020 16:00|05-06-2020 23:00|07:00|01:00 (btn 22 to 06)

N10002 |BBB |06-06-2020 23:00|07-06-2020 08:00|09:00|08:00 (btn 22 to 06)

N10006 |XYX |08-06-2020 03:00|09-06-2020 23:30|20:30|03:00 (btn 22 to 06)

N10010 |LMN |08-06-2020 21:00|08-06-2020 23:45|02:45|01:45 (btn 22 to 06)

Hello!

The NOW and TIME functions are not used when calculating the time difference. See the answer to your question here.

I have sum all my employee working house with =SUM(C2:C26), I got a total of 810:07 hours (format [h]:mm) When I tried to change the formatting to

dd "day," h: "hours," mm "minutes. I got 2 days, 18 hours 07 minutes which is wrong! How to fix that?

Thanks

Hello Khalid!

The date format in Excel cannot show the number of days greater than 31. Therefore, try to use such a custom time format

m"month," d"day," hh"hours," mm"minutes"