1

I am trying to enter a negative time value into a cell and receive the error "The formula you typed contains an error." I have tried changing to the 1904 date system but receive the same error. The cell is formatted as [h]:mm, I have also tried hh:mm but still having the same issue.

fixer1234
  • 27,064
  • 61
  • 75
  • 116
Bob
  • 21
  • 3
  • 2
    [Entering Negative Times](http://excelribbon.tips.net/T013356_Entering_Negative_Times.html) is the first search result for "excel Enter negative time into cell". Please read [How do I ask a good question?](http://superuser.com/help/how-to-ask): "Have you thoroughly searched for an answer before asking your question? Sharing your research helps everyone. Tell us what you found and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers" – DavidPostill Nov 05 '15 at 16:07
  • 2
    Possible duplicate of [Is there any way to properly display negative time spans in Excel?](http://superuser.com/questions/205770/is-there-any-way-to-properly-display-negative-time-spans-in-excel) – DavidPostill Nov 05 '15 at 16:09
  • 2
    @DavidPostill: To be fair that article says you still cannot enter negative times using the 1904 workaround. – qasdfdsaq Nov 05 '15 at 16:09
  • @qasdfdsaq Yes, but it explains why and shows a workaround. – DavidPostill Nov 05 '15 at 16:11
  • [MS-Excel Negative times](http://stackoverflow.com/q/2197237) – DavidPostill Nov 05 '15 at 16:13
  • [Display Negative Time Values](http://www.brainbell.com/tutorials/ms-office/excel/Display_Negative_Time_Values.htm) - three different solutions – DavidPostill Nov 05 '15 at 16:16
  • 2
    @DavidPostill: No, it says there is no workaround. All those solutions tell you how to *display* negative times, not *enter* them. – qasdfdsaq Nov 05 '15 at 16:18
  • @qasdfdsaq Enter a positive time and Format » Cells » Custom and use a Custom format of -h:mm. – DavidPostill Nov 05 '15 at 16:22
  • 2
    @DavidPostill: That'll just give you incorrect results in formulas because because it isn't actually storing a negative time. Also it makes it impossible to enter *positive* times. – qasdfdsaq Nov 05 '15 at 16:26
  • Thank you for the suggestions. It appears I cannot get the functionality I am looking for. – Bob Nov 05 '15 at 16:29
  • @qasdfdsaq Entering a negative time in a cell was what the OP asked for. If the OP wants more he needs to expand his question. The links I've provided a certainly starting points for the OP to look at for any further needs. – DavidPostill Nov 05 '15 at 16:31
  • 2
    @DavidPostill: And none of those answers allow you to enter a negative time. All they say is you can't. *None* of them provide a solution *or* workaround. – qasdfdsaq Nov 05 '15 at 16:33
  • Thank you for the suggestions. It appears I cannot get the functionality I am looking for. This is for a flexi time spreadsheet I am working on that has a list of all the agents on a team in column A and in row 1 a list of the dates in that particular month. An example: I was looking to be able to type in -06:42 (taking hours off) in one cell, 07:00 (working extra time for credit/working time back) in the next etc and at the end have =sum(B2:AE2) and it would give a total of if the person was in credit or due time back. I will need to come up with a different solution. – Bob Nov 05 '15 at 16:37
  • @Bob: The closest workaround I can think of is to have another cell somewhere in the workbook or another workbook that converts a time you enter into a negative dotted decimal, which you can then copy and paste (or type) into your target column. – qasdfdsaq Nov 05 '15 at 16:39

0 Answers0